Forum Discussion
Advocate IDAX RANKX: Rank Column that ranks based on slicer selection?
I have a slicer based on Table[Color] and a ranking measure that ranks items based on Table[Size]. When all colors are selected in the slicer it ranks every item. When a specific color is selected, it ranks only the items for that color starting from 1. This is exactly what I need. My problem using the Rank Measure is that Power BI will not let me use the Rank Measure as an axis in my visuals.
I know that I can use a calculated column as an axis for the visuals. So to work around the original problem, I tried to create a calculated column (Rank Column) that copies the Rank Measure. The calculated column works in ranking all items but when using the slicer, it does not rank items starting from 1, it just gives their overall rank based on all colors. How can I get the Calculated Column to rank starting from 1 based on the slicer selection?
Below is the DAX for the Rank Measure and Rank Column.
_____________________
______________________
______________________
Hi smore,
Do you want to show the overall ranking after applying the slicer?
Here is the output:
Try measure as:
Rank Measure = RANKX( ALL('Table'), CALCULATE(SUM('Table'[Size])) ,, DESC )Best Regards,
Link
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- amitchandak
Super User
smore ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
smoreI am not sure how to upload a pbix on the forum so here is a google drive link to a sample.pbix:
https://drive.google.com/file/d/1EeEjuF9_iQCPQIEeS7bZVKmOIii8GqEE/view?usp=sharing
After preparing the sample pbix I realized that the Rank Column is not ranking properly. I am not sure what is wrong with the DAX code for the Rank Column. I just want it to work the same as the Rank Measure. Thanks for your reply.
- v-xulin-mstf
Community Support
Hi smore,
Do you want to show the overall ranking after applying the slicer?
Here is the output:
Try measure as:
Rank Measure = RANKX( ALL('Table'), CALCULATE(SUM('Table'[Size])) ,, DESC )Best Regards,
Link
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
