Forum Discussion
How to handle Duplicate Rankings Using the RANKX DAX Function (No unique identifier)
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!!
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"
2 Replies
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"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.
