The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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!
Solved! Go to 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
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
Thanks! That worked! I was lost in the row context... Your code helped me understand where I was off on the wrong path!
User | Count |
---|---|
28 | |
12 | |
8 | |
7 | |
5 |
User | Count |
---|---|
36 | |
14 | |
12 | |
7 | |
7 |