Counting consecutive values

[davidgaribaldi]

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 the chain of values in a calculated column. For example:

ID, Date, Value, Consecutive Values

1, 1/1/2019, A, 0

1, 1/2/2019, A, 2

2, 1/1/2019, P, 1

2, 1/2/2019, A, 1

3, 1/1/2019, P, 0

3, 1/2/2019, P, 2

I hope that isn't too confusing.

[dax]

Hi davidgaribaldi,

I don't understand the logic of your sample, so if possible, could you please  expain this to me? Then I will help you more corretly.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

[PattemManohar]

@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]

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 · Accepted solution]

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.