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.
Hi Guys,
So here is my problem, below is a table with events related to a project at the given times. What I need is to create a RANK column (as shown below) based on event time per project. The table should illustrate this clearly, please remember I am in directquery mode.
Project - EVENTID - TIME - RANK
A - 1234 - 1/1/2017 - 1
A - 2345 - 1/7/2017 - 2
A - 3456 - 1/14/2017 - 3
B - 7890 - 6/1/2017 - 1
B - 8901 - 6/7/2017 - 2
B - 9012 - 6/14/2017 - 3
C - 6543 - 9/1/2017 -1
D - 9876 - 8/1/2017 -1
D - 7865 - 8/7/2017 -2
Any help here would be greatly appreciated.
Thanks,
Moiz
Solved! Go to Solution.
@moizsherwani wrote:
Hi Guys,
So here is my problem, below is a table with events related to a project at the given times. What I need is to create a RANK column (as shown below) based on event time per project. The table should illustrate this clearly, please remember I am in directquery mode.
Project - EVENTID - TIME - RANK
A - 1234 - 1/1/2017 - 1
A - 2345 - 1/7/2017 - 2
A - 3456 - 1/14/2017 - 3
B - 7890 - 6/1/2017 - 1
B - 8901 - 6/7/2017 - 2
B - 9012 - 6/14/2017 - 3
C - 6543 - 9/1/2017 -1
D - 9876 - 8/1/2017 -1
D - 7865 - 8/7/2017 -2
Any help here would be greatly appreciated.
Thanks,
Moiz
Try to create a measure as below. As to the "-1", it looks that there's some issue when sorting asc in RANKX, the rank starts from 2, instead of 1.
rank = RANKX ( ALLEXCEPT ( r, r[project] ), CALCULATE ( MIN ( r[time] ), ALLEXCEPT ( r, r[project], r[time] ) ), , ASC, DENSE ) -1
@moizsherwani wrote:
Hi Guys,
So here is my problem, below is a table with events related to a project at the given times. What I need is to create a RANK column (as shown below) based on event time per project. The table should illustrate this clearly, please remember I am in directquery mode.
Project - EVENTID - TIME - RANK
A - 1234 - 1/1/2017 - 1
A - 2345 - 1/7/2017 - 2
A - 3456 - 1/14/2017 - 3
B - 7890 - 6/1/2017 - 1
B - 8901 - 6/7/2017 - 2
B - 9012 - 6/14/2017 - 3
C - 6543 - 9/1/2017 -1
D - 9876 - 8/1/2017 -1
D - 7865 - 8/7/2017 -2
Any help here would be greatly appreciated.
Thanks,
Moiz
Try to create a measure as below. As to the "-1", it looks that there's some issue when sorting asc in RANKX, the rank starts from 2, instead of 1.
rank = RANKX ( ALLEXCEPT ( r, r[project] ), CALCULATE ( MIN ( r[time] ), ALLEXCEPT ( r, r[project], r[time] ) ), , ASC, DENSE ) -1
@Eric_Zhang O M G! that is the best solution ever. Can you please do me a massive favor and explain the logic here, I would be ever so grateful.
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |