Forum Discussion

MattiaMaetini's avatar
MattiaMaetini
Icon for Helper I rankHelper I
2 years ago

RANKX LIVE CONNECTION SSAS Tabular 2017 - DRILLDOWN MATRIX without ISINSCOPE DAX FUNCTION AVAILABLE

Hi expert, it's all into Subject! 😅😂 I'm going crazy about this requirement: I need to create a dinamyc rank in a Matrix with 3 level of drill down. I was able to create rank using RANKX fucti...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi MattiaMaetini ,

I think you can use the ISFILTERED function in combination with other DAX functions to achieve the desired behavior. For example, you can create a measure that checks if a certain level of your hierarchy is filtered and then apply the RANKX function accordingly. Here’s a conceptual example of how you might structure such a measure:

DynamicRank = 
VAR CurrentLevel = 
    SWITCH(
        TRUE(),
        ISFILTERED(Table[Level1]), "Level1",
        ISFILTERED(Table[Level2]), "Level2",
        ISFILTERED(Table[Level3]), "Level3",
        "Total"
    )
RETURN
    SWITCH(
        CurrentLevel,
        "Level1", RANKX(ALL(Table[Level1]), CALCULATE(SUM(Table[Measure]))),
        "Level2", RANKX(ALL(Table[Level2]), CALCULATE(SUM(Table[Measure]))),
        "Level3", RANKX(ALL(Table[Level3]), CALCULATE(SUM(Table[Measure]))),
        BLANK()
    )

 

 

 

Best Regards

Yilong Zhou

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