Rack Count

[adarshmp8998]

Hi Everyone,

I trying to find the to total rack count with the dax, can anyone help me to write a dax in power bi.

current state is 7 and the next state is 8. If so, it adds 1 to the rack count.

# should not consider current state = 8 and the next state = 7 or state = 7 or state = 8

State	timestamp
7	01-02-2025 00:04:06
7	01-02-2025 00:04:11
7	01-02-2025 00:04:15
8	01-02-2025 00:04:23
8	01-02-2025 00:04:43
1	01-02-2025 00:04:51
1	01-02-2025 00:05:35
1	01-02-2025 00:14:51
7	01-02-2025 00:14:59
7	01-02-2025 00:15:06
7	01-02-2025 00:15:39
7	01-02-2025 00:20:47
7	01-02-2025 00:20:55
8	01-02-2025 00:21:06
8	01-02-2025 00:21:35
8	01-02-2025 00:21:43
9	01-02-2025 00:21:47
7	01-02-2025 00:21:51
7	01-02-2025 00:21:55
7	01-02-2025 00:22:31
7	01-02-2025 00:22:35
8	01-02-2025 00:22:39
8	01-02-2025 00:22:43
1	01-02-2025 00:22:50
1	01-02-2025 00:22:59
9	01-02-2025 00:23:19
7	01-02-2025 00:23:27
7	01-02-2025 00:23:31
7	01-02-2025 00:23:35
8	01-02-2025 00:24:15
8	01-02-2025 00:24:19
1	01-02-2025 00:24:27
8	01-02-2025 00:21:06
8	01-02-2025 00:21:06
8	01-02-2025 00:21:06
1	01-02-2025 00:21:06
1	01-02-2025 00:21:06
1	01-02-2025 00:21:06
7	01-02-2025 00:21:06
7	01-02-2025 00:21:06
1	01-02-2025 00:21:06
1	01-02-2025 00:21:06
8	01-02-2025 00:21:06
8	01-02-2025 00:21:06
7	01-02-2025 00:21:06
7	01-02-2025 00:21:06
1	01-02-2025 00:21:06

[adarshmp8998]

@Greg_Deckler ,

Total count of rack should be 4. Below group will be counted as 1 rack and it should follow the sequence, 7 and then 8

7	01-02-2025 00:04:06
7	01-02-2025 00:04:11
7	01-02-2025 00:04:15
8	01-02-2025 00:04:23
8	01-02-2025 00:04:43

[Greg_Deckler]

@adarshmp8998 OK, so you the min date and time and then you are looking for where the next row is 0 or +1 different from that. So what are the other 4 racks? I still don't understand the logic here. Is it only the instances where it goes form 7 to 8 that count? So if there are consecutive 7's but no 8 then that does not count? What about the 1's? 

Seems similar to Cthulhu/Streaks. Streaks! - Microsoft Fabric Community. Off the cuff, maybe:

[adarshmp8998 · Accepted solution]

@Greg_Deckler ,

I used below method to solve it,

A) created Index calculated column

Index = RANKX('sampledf', 'sampledf'[timestamp], , ASC, DENSE)

B) created NextState calculated column

NextState =

VAR CurrentIndex = 'sampledf'[Index]

RETURN CALCULATE(

    MAX('sampledf'[State]),

    FILTER(

        'sampledf',

        'sampledf'[Index] = CurrentIndex + 1

    )

)

C) Written measure to calculate the Total columns

RackCount =

SUMX(

    'sampledf',

    IF(

        'sampledf'[State] = 7 && 'sampledf'[NextState] = 8,

        1,

        0

    )

)

[Greg_Deckler]

@adarshmp8998 So what is the expected output from this?