Ranking (RANKX) the output of measures

[Anonymous]

Hi community,

Is it possible to use RANKX or similar DAX measure or another method to rank the output of measures?

I currently have 7 measures in a multi-row card visual to display the top concerns that I’d like to rank / sort by top output to its lowest output. If this is possible, is it also possible to ensure this ranking is kept dynamically? (i.e. when using filter / slicer)

Currently I’ve manually sorted it through the field in the visualization panel, but this is not dynamic and will have to be kept updated if the % change when the dashboard is refreshed.

[OwenAuger · Accepted solution]

Hi @Anonymous 

To be able to have the percentages sorted dynamically, rather than including multiple measures on the visual, you must have:

• A "Concern" column in a table containing the various Concern values: "Road Closure", "Traffic" etc.
  This could either be a column an existing table (if that is how the data is structured), or a specially created parameter table.
• A single measure that returns the correct percentage when filtered by the values in the above column.

Then, on the multi-row card, you would include the "Concern" column and the measure.

The appearance of the visual would be a little different to your existing setup, but pretty similar.

I have attached a PBIX file showing two possible approaches.

1. If you have a table already containing Concern column and you can create a generic percentage measure that will respond to filters on this column, then simply place the column and measure in a multi-row card.

2. If you want to make use of the existing measures, you can create a parameter table that contains a column with each possible Concern value, and a measure that switches based on the selected Concern value.
Similar to this article.

Selected Concern = 
SELECTEDVALUE ( 'Concern parameter'[Concern] )

Selected Concern Percentage = 
SWITCH (
    [Selected Concern],
    "Air Quality", [Air Quality],
    "Lighting", [Lighting],
    "Mud on the roads", [Mud on the roads],
    "Noise", [Noise],
    "Personal Behaviour", [Personal Behaviour],
    "Road Closure", [Road Closure],
    "Traffic", [Traffic]
)

Once placed in the multi-row card visual, the sorting can be set in the "More options" menu.

Regards,

Owen

[Anonymous]

That worked perfectly, thank you.