Forum Discussion
Dynamic ranking by different fields
Hello,
I am trying to get the ranking of a sum by choosing the field but unfortunately, I do not know how to create a dynamic ranking dax.
Here I attach the data. As we can see, there are three fields of category and finally another with numbers which will be used to obtain a measury with the summatory.
PersonCountryItemAmount
| Mike | Spain | shirts | 1 |
| John | France | pants | 2 |
| Peter | Italy | shoes | 3 |
| John | Greece | hats | 4 |
| Mike | Spain | belts | 5 |
| John | France | shirts | 6 |
| Susan | Italy | pants | 7 |
| Mike | Spain | pants | 8 |
| Peter | France | shoes | 9 |
| Sam | Germany | belts | 10 |
| Mike | Spain | hats | 1 |
| Susan | Italy | pants | 2 |
| John | Greece | shirts | 3 |
| Mike | France | belts | 4 |
| Peter | Spain | shoes | 5 |
| Sam | Germany | pants | 6 |
| Susan | Italy | belts | 7 |
| Mike | France | pants | 8 |
| John | Spain | hats | 9 |
| Sam | Greece | belts | 10 |
| Susan | Germany | shoes | 1 |
| Mike | Spain | shirts | 2 |
| John | France | belts | 3 |
| Sam | Italy | hats | 4 |
| Mike | Germany | pants | 5 |
| Susan | France | shoes | 6 |
| John | Greece | belts | 7 |
| Mike | Spain | hats | 8 |
| John | France | belts | 9 |
| Peter | Germany | pants | 10 |
| Sam | Spain | shirts | 1 |
| Susan | France | shoes | 2 |
In order to be able to filter by field, I have created a parameter which allow us to choose which field use to obtain the summatory.
However, I cannot get the ranking of these in a dynamic way.
Here the parameter table created with all formulas used.
As we can see in the board, the dynamic ranking does not work as it woul be desired.
Is there a way to set it dynamically in order to avoid extra dax measures and have only one ranking column in the table?
Thank you in advance!
Hi, jmateu88 Please use the below measure, which dynamically rank different fields.
Rank =VAR _selectedValue = SELECTEDVALUE(Parameter[Parameter Order])VAR _rankCountry = RANKX(ALLSELECTED('Table'[Country]), [TotalAmount])VAR _rankItem = RANKX(ALLSELECTED('Table'[Item]), [TotalAmount])VAR _rankPerson = RANKX(ALLSELECTED('Table'[Person]), [TotalAmount])VAR _Result =SWITCH(TRUE(),_selectedValue=0, _rankItem,_selectedValue=1, _rankPerson,_rankCountry)RETURN_Result
Hope this helps!!
If this solved your problem, please accept it as a solution!!
2 Replies
Hi, jmateu88 Please use the below measure, which dynamically rank different fields.
Rank =VAR _selectedValue = SELECTEDVALUE(Parameter[Parameter Order])VAR _rankCountry = RANKX(ALLSELECTED('Table'[Country]), [TotalAmount])VAR _rankItem = RANKX(ALLSELECTED('Table'[Item]), [TotalAmount])VAR _rankPerson = RANKX(ALLSELECTED('Table'[Person]), [TotalAmount])VAR _Result =SWITCH(TRUE(),_selectedValue=0, _rankItem,_selectedValue=1, _rankPerson,_rankCountry)RETURN_Result
Hope this helps!!
If this solved your problem, please accept it as a solution!!
