Dynamic Ranking with field parameters in DAX

Recently on Microsoft Fabric PowerBI forum I came across one of a kind requirement which was a first for me.

The original poster wanted dynamic rankings based on field parameters. I am familiar with dynamically adjusting the rankings based on slicer selection but making the ranking dynamic based on the selection in field parameter was a new one for me.

So I thought why not make a short blog post on it.

Check out the original requirement in the link below.

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dynamically-rank-calculated-measure-based-on-different-groupings/m-p/4264507

You can find the solution I provided below.

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dynamically-rank-calculated-measure-based-on-different-groupings/m-p/4264507/highlight/true#M169018

In this blog post I will try to simplify and explain the issue with the solution.

If the GIFs in this blog appear too blurry, please right-click on them and open them in a new tab for a clear view.

Use Case

Lets say your data has two columns id & name

id name
1,name1
2,name2
3,name3

To rank the rows you could do it in measure using the DAX RANKX function and display the measure on a third column. Let’s name the new column as RankColumn.

RankColumn=
    RANKX (
        ALLSELECTED('Table'[Name] ),
        CALCULATE ( VALUES ( 'Table'[id] ) ),
        ,
        ASC,
        DENSE
    )

The measure would make the rankings dynamic based on the selection in the slicer.

Focus on the RankColumn on how its value changes dynamically to display the ranks in the correct order.

This looks simple and straightforward. But complication with ranking starts when you want to add a field parameter that allows you to dynamically select the column to slice and dice the values in the visual.

Lets modify our original data and add a third column Country

Table = DATATABLE(
       "id", INTEGER,
       "Name",STRING,
       "Country",STRING,    
       {
        {1,"Name1","US"},
        {2,"Name2","Canada"},
        {3,"Name3","China"}
    }

Now create a field parameter to give the end user the flexibility to dynamically slice and dice the data through two columns Name and Country.

This will create a field parameter called Parameter. You can rename it if you want.

Adding this parameter to the report, the ranking works well for the Name column but for Country it is all messed up. Focus on the RankColumn in the GIF below.

So how to fix this ?

The Solution

To make this work ,we will have to fix the original ranking measure to account the selection dynamicity of the field parameters.

RankColumn = 
  Var NameRank = RANKX (
        ALLSELECTED('Table'[Name] ),
        CALCULATE ( VALUES ( 'Table'[id] ) ),
        ,
        ASC,
        DENSE
    ) 

    Var CountryRank = RANKX (
        ALLSELECTED('Table'[Country] ),
        CALCULATE ( VALUES ( 'Table'[id] ) ),
        ,
        ASC,
        DENSE
    ) 

    RETURN SWITCH(TRUE(), 
     CONTAINSSTRING ( SELECTEDVALUE ( Parameter[Parameter Fields] ), "Name" ), NameRank,
     CONTAINSSTRING ( SELECTEDVALUE ( Parameter[Parameter Fields] ), "Country" ), CountryRank)

What we did was , we added two ranking variables in the measure and based on the selection in the slicer we swap them by checking the selected value of the field parameter.

So in essence, based on the number of fields in your field parameters you have to add that many variables to your ranking function and swap them accordingly through the SWITCH function that is based on selection of the parameter slicer.

Conclusion

I tried to simplify the problem in much more meaningful way, though I am sure the real business use cases can be quite complex in such scenarios. I hope this post provides some useful insight into how to dynamically managing ranking with field parameters.