Forum Discussion
StuartSmith
Power Participant
Power ParticipantCant get RANK to work correctly.
I have a table similar to ... and trying to create a calculated column Ranked by ID and AbsenceDate
| ID | AbsenceDate | Name | Required Rank | RANKX | RANK | |||||
| 1 | 01/01/2024 | John Doe | 1 | 1 | 1 | |||||
| 1 | 02/01/2024 | John Doe | 2 | 1 | 2 | |||||
| 1 | 03/01/2024 | John Doe | 3 | 1 | 3 | |||||
| 1 | 04/01/2024 | John Doe | 4 | 1 | 4 | |||||
| 2 | 01/01/2024 | Jane Doe | 1 | 1 | 5 | |||||
| 2 | 02/01/2024 | Jane Doe | 2 | 1 | 6 | |||||
| 2 | 03/01/2024 | Jane Doe | 3 | 1 | 7 |
I have tried various things such as and they give the results above, but I want the required results.
RANKX= RANKX(ALLSELECTED('Table: Absence Master List'[Absence Recording ID]), 'Table: Absence Master List'[Absence Day],,DESC, Dense)
RANK = RANK(SKIP,ALLSELECTED('Table: Absence Master List'[Absence Recording ID], 'Table: Absence Master List'[Absence Day]))
Thanks in advance.
5 Replies
- Ahmedx
Super User
pls try this
StuartSmithAdjusted the above and this worked. 🙂
RANK =VAR _t1 = 'Table: Absence Master List'[Absence Recording ID]ReturnRANKX(FILTER('Table: Absence Master List', 'Table: Absence Master List'[Absence Recording ID] = _t1),'Table: Absence Master List'[Absence Day],,ASC)
- Daniel29195
Community Champion
Measure 3 =var dataource =CALCULATETABLE(SUMMARIZE(table3,table3[ID],table3[Name],table3[AbsenceDate]),ALLEXCEPT(table3,table3[ID]))var result =RANKX(dataource,calculate(max(table3[AbsenceDate])),,DESC,Dense)return resultIf my response has successfully addressed your issue kindly consider marking it as the accepted solution! This will help others find it quickly. Dont forget to hit that thumbs up button 🫡👍
- Ahmedx
and try this
- StuartSmith
Thanks, but when transfered to my live report it created a circular error., but you other solution worked. 🙂
