Forum Discussion
Status Tracking
Hello everyone, I have the following table;
ID Date Status
| 1 | 05/05/2023 | A |
| 1 | 06/05/2023 | B |
| 2 | 01/02/2023 | A |
| 2 | 01/04/2023 | C |
| 3 | 05/05/2023 | A |
| 3 | 06/05/2023 | C |
I would like to define the following ;
Measure 1 = COUNT (Status A to Status B) = 1 Total
Measure 2 = COUNT (Status A to Status C) = 2 Total
Which would allow to me to count how many unique IDs have changed from A to B and from A to C over time.
Could anyone provide me with some help on this?
Many Thanks
Hi Anonymous
Dax formula for your first request :Distinct Count A To B =CALCULATE(DISTINCTCOUNT('Table'[ID]),FILTER('Table','Table'[Status] = "B" &&CALCULATE(MAX('Table'[Date]),FILTER('Table','Table'[ID] = EARLIER('Table'[ID]) &&'Table'[Status] = "A")) <'Table'[Date]))For Second :Distinct Count A To C =CALCULATE(DISTINCTCOUNT('Table'[ID]),FILTER('Table','Table'[Status] = "C" &&CALCULATE(MAX('Table'[Date]),FILTER('Table','Table'[ID] = EARLIER('Table'[ID]) &&'Table'[Status] = "A")) <'Table'[Date]))If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
3 Replies
Hi Anonymous
Dax formula for your first request :Distinct Count A To B =CALCULATE(DISTINCTCOUNT('Table'[ID]),FILTER('Table','Table'[Status] = "B" &&CALCULATE(MAX('Table'[Date]),FILTER('Table','Table'[ID] = EARLIER('Table'[ID]) &&'Table'[Status] = "A")) <'Table'[Date]))For Second :Distinct Count A To C =CALCULATE(DISTINCTCOUNT('Table'[ID]),FILTER('Table','Table'[Status] = "C" &&CALCULATE(MAX('Table'[Date]),FILTER('Table','Table'[ID] = EARLIER('Table'[ID]) &&'Table'[Status] = "A")) <'Table'[Date]))If this post helps, then please consider Accept it as the solution to help the other members find it more quicklyIt was my pleasure to assist 🙂