Forum Discussion

Chris_23's avatar
Chris_23
Icon for Helper II rankHelper II
1 year ago
Solved

RANKX Measure should ignoring Higher-Level Context for Independent Ranking

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

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.

  • Chris_23's avatar
    Chris_23
    Icon for Helper II rankHelper II
    1 year ago

    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's avatar
      Anonymous
      Not applicable
      1 year ago

      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's avatar
        Chris_23
        Icon for Helper II rankHelper II
        1 year ago

        Thank you very much for the great solution