How to give dynamic ranking with respect to other columns using DAX or power query

[Nicks612]

Hello everyone,

I want to generate a ranking column in Table A with respect to the Sr.No and DBkey columns.

Ex. for Sr.No = 123456, ranking must be given according to the DBKey column in ascending sorting which means the smallest DBKey value for the given Sr.No is '20230503070939' so the Rank column should display '1' against the DBKey value.

then,

the DBKey '20230503084725' is the 2nd largest number so the Rank column should display '2' against the DBKey value.

then,

the DBKey '20230503092547' is the 3rd largest number so the Rank column should display '3' against the DBKey value.

like that it should display for all other Sr.No.

you can understand the requirements by reviewing the below tables

Original Data:

Table A
Sr.No	DBKey
123456	20230503092547
123456	20230503070939
123456	20230503084725
235678	20230508100137
132456	20230524172714
132456	20230527141557
165478	20230514120545
165478	20230513100529
165478	20230514153705
165478	20230514194523
165478	20230514083357
765432	20230507130646
765432	20230507131046

Result Table:

Table A
Sr.No	DBKey	Rank
123456	20230503092547	3
123456	20230503070939	1
123456	20230503084725	2
235678	20230508100137	1
132456	20230524172714	1
132456	20230527141557	2
165478	20230514120545	3
165478	20230513100529	1
165478	20230514153705	4
165478	20230514194523	5
165478	20230514083357	2
765432	20230507130646	1
765432	20230507131046	2

I want the solution by using DAX and by power query editor.  Kindly, guide me for the mentioned query.

thanks in Advance!

[lbendlin]

Here is the Power Query version. I hope that it is clear the Power Query is not dynamic.  Its results are not impacted by user interaction with filters.

Note that the ranking result differs from your expected results.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc+xDcQwDAPAXVynoEjRsmcJsv8a738EyBdOqwMp6TxbUOnejkZQMIRJZ7Xr2Flhar7YyKJ/RrnXeGwEELo7xf8cM4oVubU1D/vO9XXV07mEcHprWvvM+ZKzCvtcxkxTe8OQ7ltqmfj8VyH07G8W+Nr1AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Sr.No = _t, DBKey = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Sr.No"}, {{"Rows", each _, type table [Sr.No=nullable text, DBKey=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddRankColumn([Rows],"Rank",{"DBKey", Order.Ascending},  [RankKind = RankKind.Competition])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"DBKey", "Rank"}, {"DBKey", "Rank"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Custom",{"Sr.No", "DBKey", "Rank"})
in
    #"Removed Other Columns"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".