# 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](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](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`

```abap
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`.

```abap
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.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1731070785367/001c1ef2-8267-4d2f-9d8b-1b2eb1921d0b.gif align="center")

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`

```abap
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`.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1731072361624/4ba621a2-9ec5-4a8b-aa0a-8820308301ce.png align="left")

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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1731072547403/1595e81c-d8cb-4d45-80f1-f809333ddf0b.png align="left")

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1731072508979/13e4f1bd-3a84-4015-83ab-69b88bdc16a9.png align="center")

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.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1731074542112/d6e77dbd-dab5-4426-9020-811fd4c4082d.gif align="left")

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.

```abap
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.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1731076718671/4526fa9d-39bb-4419-8745-ac3189ec9a50.gif align="center")

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.