topic DAX Ranking over a group, status flag and sequence columns in DAX Commands and Tips

DAX Ranking over a group, status flag and sequence columns

mrskool — Thu, 05 Sep 2019 18:43:32 GMT

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!

Re: DAX Ranking over a group, status flag and sequence columns

AlB — Thu, 05 Sep 2019 19:33:28 GMT

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

Re: DAX Ranking over a group, status flag and sequence columns

mrskool — Fri, 06 Sep 2019 13:20:40 GMT

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