Forum Discussion
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 insurance type along with the maximum date.
As per my original table VAR, I am trying to connect that value back to together but struggled. In my summarize statement, if I introduce [Insurance type], it returns the maximum date along with the insurance type. This returns more values. I just want the maximum date and then whatever the insurance type is return that in a table.
This also could just be a calculated column as well perhaps.
MaxInsuranceStatus =
VAR vBaseTable =
ADDCOLUMNS (
SUMMARIZE (
'Employee List',
'Employee List'[Employee number],
'Employee List'[Employee name]
),
"@Date", MAX ( 'Employee List'[Insurance Date] )
)
VAR vRankTable =
ADDCOLUMNS ( vBaseTable, "@Rank", RANKX ( vBaseTable, [@Date],, DESC, DENSE ) )
VAR vTopRank =
FILTER ( vRankTable, [@Rank] = 1 )
Var OriginalTable =
SELECTCOLUMNS('Employee List',"Employee number",'Employee List'[Employee number],"Insurance Date",'Employee List'[Insurance Date],"Vaccination type",'Employee List'[Insurance type])
//Var FilterOriginal =
//FILTER(vTopRank,[@Date] = OriginalTable
RETURN
vTopRank
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)
3 Replies
tvalente , 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-columnsThanks,
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)RETURNRANKX (EmployeesGroup,'Employee List'[Vaccine Date],,DESC)
