Forum Discussion

davidgaribaldi's avatar
davidgaribaldi
Helper I
6 years ago
Solved

Counting consecutive values

Hi I'm trying to count consecutive rows that match.   I have an ID column, a date column, and then a value column. I'm sorting by the ID then the date and I'd like for it to tell me the length of t...
Need Help
  • dax's avatar
    dax
    6 years ago

    Hi davidgaribaldi,

    You could try below measure to see whether it work or not

    Measure 3 =
VAR temp =
    CALCULATE (
        COUNT ( t5[ID] ),
        FILTER ( ALLEXCEPT ( t5, t5[ID], t5[ Value] ), t5[ Date] <= MIN ( t5[ Date] ) )
    )
RETURN
    IF (
        CALCULATE ( COUNT ( t5[ Value] ), ALLEXCEPT ( t5, t5[ID], t5[ Value] ) ) > 1
            && temp = 1,
        0,
        temp
    )

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

PattemManohar's avatar
PattemManohar
Community Champion
6 years ago
davidgaribaldi Just a quick question, why you have the second duplicate row as 2 instead of 1 as your chain starts at 0.

For Example, 1, 1/2/2019, A, 2 (this should be 1, as the previous same row started it's count from 0)
davidgaribaldi's avatar
davidgaribaldi
Helper I
6 years ago

Because I only want the end number so I don't want it to increment I just want 0's and the last number in the chain with the amount of that value. I'd gladly settle for something like 1, 1/1/19, A, 2; 1, 1/2/19, A, 2

 

Basically I want to be able to tell the amount of 'A's' and 'P's' in each chain quickly.

 

If you can think of a more elegant way to accomplish this I'm open to it.

  • dax's avatar
    dax
    Community Support
    6 years ago

    Hi davidgaribaldi,

    You could try below measure to see whether it work or not

    Measure 3 =
VAR temp =
    CALCULATE (
        COUNT ( t5[ID] ),
        FILTER ( ALLEXCEPT ( t5, t5[ID], t5[ Value] ), t5[ Date] <= MIN ( t5[ Date] ) )
    )
RETURN
    IF (
        CALCULATE ( COUNT ( t5[ Value] ), ALLEXCEPT ( t5, t5[ID], t5[ Value] ) ) > 1
            && temp = 1,
        0,
        temp
    )

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.