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 cou...
"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
        )
    )
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
    )
)