Forum Discussion

cinderelly678's avatar
cinderelly678
Frequent Visitor
3 years ago
Solved

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....
Need Help
Tips and Tricks
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    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
Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

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