Rank calculation based on dynamic dimension parameter

[Madhusmita]

HI Team,

Can anyone please help me with the below Rank calculation. 

Rank = RANKX(ALL(RESULTS[Country]),CALCULATE(SUM(RESULTS[CADRE POINTS]))) - Working

Rank = RANKX(ALL(p.Dimension[p.Dimension]),CALCULATE(SUM(RESULTS[CADRE POINTS]))) - Not Working

When i am passing the dimension name Country the rank is working fine but when i am using paramter as p.Dimension in place of Country it doesn't work.

Note: p.Dimension is a parameter that includes Country,Name,Region,Category. Basically i want to switch between these dimensions and the ranking changes based on the selected dimensions.

Thanks in advance

[Madhusmita]

This is my P. Dimension Breakdown looks like

P.Dimension Breakdown = {

    ("Name", NAMEOF('RESULTS'[Name]), 0),

    ("Region", NAMEOF('RESULTS'[Region]), 1),

    ("Country", NAMEOF('RESULTS'[Country]), 2),

    ("Agency Network", NAMEOF('RESULTS'[Agency Network]), 3),

    ("Holding Company", NAMEOF('RESULTS'[Holding Company]), 4)

}

[Madhusmita]

Hi Sahir,

as per your suggestion i created those calculations for each dimensions and tried to pass it all together but its throwing the below error. It looks like the p.Dimension breakdown parameter is not appearing when i try to use in the calculation 

[Sahir_Maharaj]

This will return the correct rank based on the selected dimension in the parameter.

Let me know if you require any further assistance.

[Sahir_Maharaj]

Similarly, you can create measures for the Region and Category dimensions. Then, you can use a calculated column to switch between these measures based on the selected dimension in the parameter. For example:

Rank =
    SWITCH(
        p.Dimension,
        "Country", [Rank by Country],
        "Name", [Rank by Name],
        "Region", [Rank by Region],
        "Category", [Rank by Category],
        BLANK()
    )

[Sahir_Maharaj]

For the Name dimension, you can create another measure:

Rank by Name = RANKX(ALL(RESULTS[Name]), CALCULATE(SUM(RESULTS[CADRE POINTS])))

[Sahir_Maharaj]

To resolve this, you can create a separate measure for each dimension that you want to use for ranking and then switch between these measures based on the selected dimension in the parameter. For example, for the Country dimension, you can create the following measure:

Rank by Country = RANKX(ALL(RESULTS[Country]), CALCULATE(SUM(RESULTS[CADRE POINTS])))

[Madhusmita]

Hi Sahir,

Thank you for the response, so, instead of All what can i use?

[Sahir_Maharaj]

Hello @Madhusmita,

The issue is happening because the RANKX function expects a table expression as the first argument. When you use ALL(RESULTS[Country]), you are passing a table expression that includes only the Country column. However, when you use ALL(p.Dimension[p.Dimension]), you are passing a table expression that includes multiple columns (Country, Name, Region, and Category).