Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
46 | |
39 | |
19 | |
19 |