RANKX Measure should ignoring Higher-Level Context for Independent Ranking

[Chris_23]

Hello everyone,

I have an issue with a RANKX measure. I want to create a ranking on the second level based on a measure, and this works well (1) for only two Levels: [Name] and [Supervisor]:

Rank1 =

  CALCULATE(

    RANKX(

        Filter(ALLSELECTED(Master[Name]), Not(ISBLANK([Sum]))),  

        [Sum],

        ,

        ASC

    ),

    REMOVEFILTERS(Master[Supervisor])

    )

However, when I add a higher level (Region), the ranking on the second (Name) level is created within the context of the first level (2) even with Removefilters on Region:

CALCULATE(

    RANKX(

        Filter(ALLSELECTED(Master[Name]), Not(ISBLANK([Sum]))),  

        [Sum],

        ,

        ASC

    ),

    REMOVEFILTERS(Master[Supervisor]),

    REMOVEFILTERS(Master[Region])

)

What I want instead is for this column to ignore the highest level and have the ranking remain independent of the Region.

RankX_3Level.pbix

Thank you very much for your help!

Chris

[Chris_23]

Hi @Anonymous 

thanks for your response.

However, that’s not quite the result I’m aiming for.

Ultimately, I’d like to achieve a ranking that is independent of the first level, Region:

Kind regards

Chris

[Anonymous · Accepted solution]

Hi @Chris_23 

Please try this:

Rank2 =
VAR _vtable =
    VAR _vtable =
        SUMMARIZE (
            FILTER ( ALLSELECTED ( 'Master' ), 'Master'[Sum] <> BLANK () ),
            Master[Name],
            "_SUM", [Sum]
        )
    RETURN
        ADDCOLUMNS ( _vtable, "_rankx", RANKX ( _vtable, [_SUM],, ASC ) )
VAR _currentName =
    SELECTEDVALUE ( Master[Name] )
RETURN
    IF (
        ISINSCOPE ( Master[Name] ),
        MAXX ( FILTER ( _vtable, 'Master'[Name] = _currentName ), [_rankx] )
    )

The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Chris_23]

Thank you very much for the great solution

[Anonymous]

Hi @Chris_23 

You can change the [Rank2] like this:

IF ( ISINSCOPE ( Master[Name] ), [Rank1] )

The ISINSCOPE() returns true when the specified column is the level in a hierarchy of levels.

You can refer to this:

ISINSCOPE function (DAX) - DAX | Microsoft Learn

The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.