Forum Discussion
DAX Ranking over a group, status flag and sequence columns
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!
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
2 Replies
- AlB
Community Champion
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!
