How to do rank partition like SQL in DAX

[tvalente]

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

[amitchandak]

@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-columns

[tvalente]

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

[tvalente · Accepted solution]

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

)