Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
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.
Hi, @abogdanov
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!!
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Jihwan, thank you for your response. Karlos, above, laid it out pretty well. thanks again
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |