DAX Ranking over a group, status flag and sequence columns

[mrskool]

Hello -

I have been trying to wrap my brain around using a DAX calculated column to get a ranking over a group by status flag and sequence. The status flag that goes between 1 and 0.   The SK, sequence and flag are all in the data source.    I am trying to get a ranking sequence that starts back at 1 whenever the flag changes.  Example:

SK	sequence	flag	looking for this ranking:
83050	1	1	1
83050	2	1	2
83050	3	0	1
83050	4	0	2
83050	5	1	1
83050	6	0	1
83050	7	0	2
83050	8	0	3
83050	9	0	4
83050	10	0	5
83050	11	0	6
83050	12	0	7
83050	13	0	8
83050	14	0	9
803051	1	1	1
803051	2	1	2
803051	3	0	1
803051	4	0	2
803051	5	0	3
803051	6	1	1
803051	7	0	1
803051	8	0	2
803051	9	0	3
803051	10	1	1
803051	11	1	2
803051	12	1	3
803051	13	1	4
803051	14	1	5

Any thoughts/ideas would be greatly appreciated!

Thanks!

[AlB · Accepted solution]

Hi @mrskool 

NewCol =
VAR PreviousFlagSeq_ =
    CALCULATE (
            MAX ( Table4[sequence] );
            Table4[flag] <> EARLIER ( Table4[flag] );
            Table4[sequence] < EARLIER ( Table4[sequence] );
            ALLEXCEPT ( Table4; Table4[SK] )
    ) + 0
RETURN
    Table4[sequence] - PreviousFlagSeq_

Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.

Cheers  

[mrskool]

Thanks!   That worked!   I was lost in the row context...  Your code helped me understand where I was off on the wrong path!