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

[jaanvikhatter]

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!! 

[AntrikshSharma]

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

[Jihwan_Kim · Accepted solution]

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"