Forum Discussion
DAX Measure Help: Counting Status Changes Over Time
I am having some trouble writing a measure which would count the number of times a status has changed for a particular item. My source is an SDC Type 2 table containing all history of status changes.
I want to make a measure that ranks the count of status changes over time. In the sample data, the grouping is by Fruit, _Timestamp, _Operation, Consumable Status, and Status Change Date. I want to make a column called "Status Change Count" with the below output.
Could anyone help me out with the DAX to accomplish this? Thank you in advance for your help.
Attached is my sample data.
Fruit_Timestamp_OperationConsumable StatusStatus Change Date
|
cinderelly678 Try:
Measure = VAR __Fruit = MAX('Table'[Fruit]) VAR __TS = MAX('Table'[Timestamp]) VAR __Table = DISTINCT(SELECTCOLUMNS(FILTER(ALL('Table'), [Fruit] = __Fruit && [Timestamp] <= __TS), "__Status", [Consumable Status])) VAR __Result = COUNTROWS(__Table) RETURN __Result
4 Replies
cinderelly678 Try:
Measure = VAR __Fruit = MAX('Table'[Fruit]) VAR __TS = MAX('Table'[Timestamp]) VAR __Table = DISTINCT(SELECTCOLUMNS(FILTER(ALL('Table'), [Fruit] = __Fruit && [Timestamp] <= __TS), "__Status", [Consumable Status])) VAR __Result = COUNTROWS(__Table) RETURN __ResultGreg_Deckler , thank you very much, that worked like a charm!
Now I have a secondary question. Let's say I wanted to iterate over the results of the Measure to flag when the measure has incremented by one over the previous value. How could I achieve that?
cinderelly678 That's Cthulhu. Cthulhu - Microsoft Fabric Community
Oh goodness -- sweet post! Let me try to wrap my head around this.
I'll likely respond with questions soon.
Thank you so much for sharing Greg_Deckler !
