Forum Discussion
Helper IHow to do rank partition like SQL in DAX
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)
Super Usertvalente , You need column rank or measure rank .
Column Rank = rankx(filter( 'Employee List', 'Employee List'[Employee number] = max('Employee List'[Employee number])) 'Employee List'[Insurance Date],,desc,dense)
measure Rank
Column Rank = rankx(allselected( 'Employee List'[Employee name], 'Employee List'[Employee number]) , max('Employee List'[Insurance Date]),,desc,dense)
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
Helper IThanks,
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 |
- tvalente
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)