Forum Discussion
Solved
How to do rank partition like SQL in DAX
Hi All, I am trying to return a column where in SQL you would rank by partition and sort the date descending. I am successfully ranking the max insurance date but am struggling to return the insu...
Found a solution from the sqlbi guys:
As below this works as a calculated column:
VaccineRank =VAR CurrentEmployee = 'Employee List'[Employee number]VAR EmployeesGroup =FILTER ('Employee List','Employee List'[Employee number] = CurrentEmployee)RETURNRANKX (EmployeesGroup,'Employee List'[Vaccine Date],,DESC)
Thanks,
This did not work.
The result was this:
| Employee name | Employee number | Insurance type | Insurance Date | Column Rank |
| ABC | 1105 | Type A | Wednesday, 11 August 2021 | 1 |
| ABC | 1105 | Type B | Wednesday, 19 May 2021 | 1 |
My desired result is this:
| Employee name | Employee number | Insurance type | Insurance Date | Column Rank |
| ABC | 1105 | Type A | Wednesday, 11 August 2021 | 1 |
| ABC | 1105 | Type B | Wednesday, 19 May 2021 | 2 |
Found a solution from the sqlbi guys:
As below this works as a calculated column:
VaccineRank =
VAR CurrentEmployee = 'Employee List'[Employee number]
VAR EmployeesGroup =
FILTER (
'Employee List',
'Employee List'[Employee number] = CurrentEmployee
)
RETURN
RANKX (
EmployeesGroup,
'Employee List'[Vaccine Date],,DESC
)