Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.