Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |