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
tvalenteThanks,
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)