How to generate the RANK with reference of two other columns - dynamic Ranking

[Nicks612]

hello everyone,

I want to generate the rank column with reference to other columns.

Please refer to the below explanation of the problem.

for example, for Sr.No = '123456', there are three DBKeys, So

for the smallest DBKey, the Rank column displays the '1'

for the 2nd smallest DBKey, the Rank column displays the '2'

for the 3rd smallest DBKey, the Rank column displays the '3'

 and so on...

like that, we need to do for other Sr.Nos also.

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

can anyone help me out? I need a solution by using DAX as well as by using a power BI query editor, if possible.

[Anonymous · Accepted solution]

Hi @Nicks612 ,

You can create a calculated column as follows.

Column = RANKX(FILTER('Table',[Sr.No]=EARLIER('Table'[Sr.No])),[DBKey],,ASC,Dense)

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[Anonymous · Accepted solution]

Hi @Nicks612 ,

Below are the results achieved using power query editor.

1) First, need to group by Sr.No column.

2) Then add a custom column with the M code Table.AddRankColumn([All],"Ranking",{"DBKey"})

3) Then Expand the custom column by unchecking Sr.No column.

4) Remove the All Column..

[Nicks612]

Thanks for the reply!! I want to know how to change the DBKey column number format to a whole number. I don't want the 2.02305E+13 format. Is there any option to convert it?  I have tried to overcome this issue by using Data Type options but have not succeeded. Please guide me on that.

[Anonymous · Accepted solution]

Hi @Nicks612 ,

You can create a calculated column as follows.

Column = RANKX(FILTER('Table',[Sr.No]=EARLIER('Table'[Sr.No])),[DBKey],,ASC,Dense)

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.