DAX Measure Help: Counting Status Changes Over Time

[cinderelly678]

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

Apples	8/8/2023 17:46	D	Spoiled Fruit - Discard	8/8/2023 0:00
Apples	8/7/2023 17:46	U	Undocked	11/8/2022 0:00
Apples	7/25/2023 17:49	U	Undocked	11/8/2022 0:00
Apples	5/31/2023 17:39	U	Undocked	11/8/2022 0:00
Apples	5/25/2023 17:38	U	Undocked	11/8/2022 0:00
Apples	5/22/2023 17:36	U	Undocked	11/8/2022 0:00
Apples	5/18/2023 17:39	U	Undocked	11/8/2022 0:00
Apples	3/17/2023 17:38	U	Undocked	11/8/2022 0:00
Apples	3/3/2023 17:33	U	Undocked	11/8/2022 0:00
Apples	2/1/2023 17:06	U	Undocked	11/8/2022 0:00
Apples	12/27/2022 17:07	U	Undocked	11/8/2022 0:00
Apples	12/6/2022 17:07	U	Undocked	11/8/2022 0:00
Apples	11/8/2022 17:07	U	Undocked	11/8/2022 0:00
Apples	10/28/2022 14:54	D	Shipped to Store	10/27/2022 0:00
Apples	10/28/2022 14:54	I	Shipped to Store	10/27/2022 0:00
Apples	10/27/2022 17:06	D	Shipped to Store	10/27/2022 0:00
Apples	10/27/2022 17:06	I	Shipped to Store	10/27/2022 0:00
Apples	10/27/2022 17:05	I	Shipped to Store	10/27/2022 0:00
Apples	10/26/2022 17:06	U	Payment Received	10/26/2022 0:00
Apples	10/24/2022 17:06	U	Purchase Order Approved	10/24/2022 0:00
Apples	10/24/2022 17:05	I	Purchase Order Received	10/24/2022 0:00
Oranges	8/7/2023 17:46	U	Undocked	11/8/2022 0:00
Oranges	7/25/2023 17:49	U	Undocked	11/8/2022 0:00
Oranges	5/31/2023 17:39	U	Undocked	11/8/2022 0:00
Oranges	5/25/2023 17:38	U	Undocked	11/8/2022 0:00
Oranges	5/22/2023 17:36	U	Undocked	11/8/2022 0:00
Oranges	5/18/2023 17:39	U	Undocked	11/8/2022 0:00
Oranges	3/17/2023 17:38	U	Undocked	11/8/2022 0:00
Oranges	3/3/2023 17:33	U	Undocked	11/8/2022 0:00
Oranges	2/1/2023 17:06	U	Undocked	11/8/2022 0:00
Oranges	12/27/2022 17:07	U	Undocked	11/8/2022 0:00
Oranges	12/6/2022 17:07	U	Undocked	11/8/2022 0:00
Oranges	11/8/2022 17:07	U	Undocked	11/8/2022 0:00
Oranges	10/28/2022 14:54	D	Shipped to Store	10/27/2022 0:00
Oranges	10/28/2022 14:54	I	Shipped to Store	10/27/2022 0:00
Oranges	10/27/2022 17:06	D	Shipped to Store	10/27/2022 0:00
Oranges	10/27/2022 17:06	I	Shipped to Store	10/27/2022 0:00
Oranges	10/27/2022 17:05	U	Shipped to Store	10/27/2022 0:00
Oranges	10/26/2022 17:06	U	Payment Received	10/26/2022 0:00
Oranges	10/24/2022 17:06	U	Purchase Order Approved	10/24/2022 0:00
Oranges	10/24/2022 17:05	I	Purchase Order Received	10/24/2022 0:00

[cinderelly678]

@Greg_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? 

[Greg_Deckler]

@cinderelly678 That's Cthulhu. Cthulhu - Microsoft Fabric Community

[cinderelly678]

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 !

[Greg_Deckler · Accepted solution]

@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