Forum Discussion
Rank based on two columns
Hi experts!
I know, there have been a lot of posts regarding this, but they have not been useful for me.
The objective is to have a ranking based on two columns.
I have a dimension table (Table Dim) that lists all of our products.
Then I have a table (Table Trans) that some transactional values in different columns.
There is the first column called Column1 and another one called Column2.
Now I would like to build a ranking for each product based on Column2 as the first priority and Column1 as the second priority.
Using this DAX Measure I get the ranking for each column:
Rank1 =
RANKX ( ALLSELECTED ( 'Table Dim'[Product] ), [Column1] )
Rank2 =
RANKX ( ALLSELECTED ( 'Table Dim'[Product] ), [Column2] )
As a result, now I have the following:
| Product | Rank1 | Rank2 | Rank Total |
| A1 | 1 | 0 | 4 |
| A5 | 2 | 2 | 2 |
| A3 | 3 | 1 | 1 |
| A4 | 4 | 3 | 3 |
| A2 | 5 | 0 | 5 |
The Rank Total column shows the result that I need.
How is this possible using DAX?
6 Replies
joshua1990 , Need some data to suggest for column1 and column 2. If they are numbers
Rank1 = RANKX ( ALLSELECTED ( 'Table Dim'[Product] ), [Column1]+ [Column2] ) Rank1 = RANKX ( ALLSELECTED ( 'Table Dim'[Product] ),calculate( [Column1]+ [Column2] ) )joshua1990 the logic for RankTotal is not clear. Please bare more details as to how you arrived there?
smpa01: Thanks for your reply.
First, the Rank Total is based on the numerical value in Rank2. Then all remaining products are based on Rank1.
joshua1990 do I understand that if Rank2 =1 then that Product is Ranked 1 in Rank Total and rest of the products need to be Ranked again as per Rank1?
joshua1990 New answers to this aren't likely to be much more useful than these older ones unless you can explain what you tried, what your code was, and what your result was. Then we can help fix it.
Otherwise, please see these:
https://community.powerbi.com/t5/Desktop/How-to-Rank-a-list-based-on-2-values-double-rankX/m-p/44008
https://community.powerbi.com/t5/Desktop/Ranking-based-on-multiple-criteria/m-p/892072
https://www.sqlbi.com/articles/rankx-on-multiple-columns-with-dax-and-power-bi/
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
Hi joshua1990 ,
Are your Rank1 and Rank2 calculated columns or measures? There are differences between the two, please refer to
Calculated Columns and Measures in DAX - SQLBI.
Please provide some sample data, which will help me create the correct formula.
For now, you can refer to this post to try to solve it.
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.
