topic Re: Rack Count in DAX Commands and Tips

Rack Count

adarshmp8998 — Thu, 27 Mar 2025 15:26:41 GMT

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

Re: Rack Count

Greg_Deckler — Thu, 27 Mar 2025 15:47:21 GMT

adarshmp8998 So what is the expected output from this?

Re: Rack Count

adarshmp8998 — Thu, 27 Mar 2025 18:37:59 GMT

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

Re: Rack Count

Greg_Deckler — Thu, 27 Mar 2025 23:21:19 GMT

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:

Re: Rack Count

adarshmp8998 — Fri, 28 Mar 2025 01:53:41 GMT

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,

)