topic How to handle Duplicate Rankings Using the RANKX DAX Function (No unique identifier) in DAX Commands and Tips

How to handle Duplicate Rankings Using the RANKX DAX Function (No unique identifier)

jaanvikhatter — Tue, 11 Feb 2025 19:15:50 GMT

Hello Everyone,

I have a data where i don't have any unique identifier, and i want to give the data a unique ranking, even for the rows where i have ID and dates as duplicated.

For example, in the table i have an ID column and a Date column where values can be duplicated. I am trying to create a Rank column but i am getting same rank for the rows where ID and Dates are same (as shown under column Rank). Required Output is the column how i want Ranking to be.

I have tried the following measure

RANKX( FILTER( 'Table', 'Table'[ID] = EARLIER('Table'[ID]) ), 'Table'[Date], , DESC )

Thanks!!

Re: How to handle Duplicate Rankings Using the RANKX DAX Function (No unique identifier)

Jihwan_Kim — Wed, 12 Feb 2025 04:27:23 GMT

Hi,

One of ways to achieve this is creating index column in power query editor.

I tried to create a sample pbix file like below, and please check the below picture and the attached pbix file.

let Source = data_source, #"Sorted Rows" = Table.Sort(Source,{{"date", Order.Ascending}}), #"Grouped Rows" = Table.Group(#"Sorted Rows", {"id"}, {{"date", each Table.AddIndexColumn(_, "new rank", 1,1)}}), #"Expanded date" = Table.ExpandTableColumn(#"Grouped Rows", "date", {"date", "new rank"}, {"date", "new rank"}), #"Changed Type" = Table.TransformColumnTypes(#"Expanded date",{{"date", type date}, {"new rank", Int64.Type}}) in #"Changed Type"

Re: How to handle Duplicate Rankings Using the RANKX DAX Function (No unique identifier)

AntrikshSharma — Wed, 12 Feb 2025 05:53:50 GMT

jaanvikhatter Is this a Global Rank or a Local Rank that resets for each ID? Are you trying to use this column in any other calculation? What is it? Running Total? Maybe you won't even need this column.