Distinct count where an ID has more than 1 value and sensitive to dates

[Anonymous]

Part of the table I am working with is in the screenshot.

What I am trying to do is to count distinct leads that contain a sequence of Analysis - Trial License . As you can see, the stage names can switch back and forth so I'd only need to look at the most recent Analysis stage and see if the same Lead ID entered the Trial License stage (they don't have to be consequent. There can be other stages between them). 

So the final code would not count Lead ID 1 because there is no Trial License stage after the most recent Analysis stage (on 7/8/2020).

Lead ID 2 would count because Trial License (1/30/2021) follows Analysis (1/25/2021).

I don't know if this is possible to implement in PBI but I've tried using DISTINCTCOUNT(IF(CONTAINS ..   and SEARCH( but still can't get what I need. Any hints would be appreciated.

[Anonymous · Accepted solution]

I think I'm following, 

You could create a calculated column that only brings over the ID's that are valid, then do a distinct count on them. for example,

Count Lead ID = 

Var _MaxofAnalysisDate = CALCULATE(MAX('Table'[Created Date]),FILTER('Table','Table'[StageName]="Analysys"),FILTER('Table','Table'[Lead ID]=EARLIER('Table'[Lead ID])))
Var _MaxofTrialLicenseDate = CALCULATE(MAX('Table'[Created Date]),FILTER('Table','Table'[StageName]="Trial License"),FILTER('Table','Table'[Lead ID]=EARLIER('Table'[Lead ID])))

Return 
IF(_MaxofTrialLicenseDate>_MaxofAnalysisDate,'Table'[Lead ID],BLANK())

Notice how ID 1 has been excluded from the new column, this is because the Max of Analysis Date is greater than the max of Trial License Date. 

[Anonymous]

That's awesome, thank you very much! I'm adding a few more conditions to prevent it from counting when MaxOfAnalysisDate is blank because PBI apparently sees it as a value and it meets the criteria of MaxofTrialLicense > MaxofAnalysisDate. 

[Jihwan_Kim]

Hi, @Anonymous 

Please correct me if I wrongly understood.

I think the measure needs something like,

For the each Lead-ID, if the last date of the Trial License is later than the last date of Analysis, then True otherwise False. Am I correct?

If it is correct, I can try to write DAX measures.

If it is OK with you, please share a sample data file, then I can try to come up with an accurate measure.

Thanks.

Did I answer your question? Mark my post as a solution!

Appreciate your Kudos!!

[Anonymous]

Jihwan, thank you for your response. Karlos, above, laid it out pretty well. thanks again