Forum Discussion

adarshmp8998's avatar
adarshmp8998
Frequent Visitor
1 year ago
Solved

Rack Count

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

 

Statetimestamp
701-02-2025 00:04:06
701-02-2025 00:04:11
701-02-2025 00:04:15
801-02-2025 00:04:23
801-02-2025 00:04:43
101-02-2025 00:04:51
101-02-2025 00:05:35
101-02-2025 00:14:51
701-02-2025 00:14:59
701-02-2025 00:15:06
701-02-2025 00:15:39
701-02-2025 00:20:47
701-02-2025 00:20:55
801-02-2025 00:21:06
801-02-2025 00:21:35
801-02-2025 00:21:43
901-02-2025 00:21:47
701-02-2025 00:21:51
701-02-2025 00:21:55
701-02-2025 00:22:31
701-02-2025 00:22:35
801-02-2025 00:22:39
801-02-2025 00:22:43
101-02-2025 00:22:50
101-02-2025 00:22:59
901-02-2025 00:23:19
701-02-2025 00:23:27
701-02-2025 00:23:31
701-02-2025 00:23:35
801-02-2025 00:24:15
801-02-2025 00:24:19
101-02-2025 00:24:27
801-02-2025 00:21:06
801-02-2025 00:21:06
801-02-2025 00:21:06
101-02-2025 00:21:06
101-02-2025 00:21:06
101-02-2025 00:21:06
701-02-2025 00:21:06
701-02-2025 00:21:06
101-02-2025 00:21:06
101-02-2025 00:21:06
801-02-2025 00:21:06
801-02-2025 00:21:06
701-02-2025 00:21:06
701-02-2025 00:21:06
101-02-2025 00:21:06
"DAX " "DAX help" 'Need help'
Urgent Help with DAX
  • adarshmp8998's avatar
    adarshmp8998
    1 year ago

    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
        )
    )

4 Replies

  • adarshmp8998's avatar
    adarshmp8998
    Frequent Visitor
    1 year ago

    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

     

    701-02-2025 00:04:06
    701-02-2025 00:04:11
    701-02-2025 00:04:15
    801-02-2025 00:04:23
    801-02-2025 00:04:43
    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      1 year ago

      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's avatar
        adarshmp8998
        Frequent Visitor
        1 year ago

        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
            )
        )