Forum Discussion
Helper IHow to generate the RANK with reference of two other columns - dynamic Ranking
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.
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.
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..
3 Replies
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.
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..
Nicks612Thanks 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.