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

[MattiaMaetini]

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 fuction on a single dimension for another table; searching on Internet I readed a lot of papers for solve the problem, but ISINSCOPE Dax function isn't available with live connection on SQLServer Tabular Analysis 2017.

Is there a workaround for solve this problem? (I tried ISFILTERED and HASONEVALUE but the behavior is not right)

BR,

M

[Anonymous]

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.

[MattiaMaetini]

Tks for your answer.

I tried to apply it, but unfortunately in my opinion the problem is that my gerarchy in Matrix is based on 3 different dim tables, so level1, level2 and level3 are always TRUE when I applied ISFILTERED() function.

PS: Another workaround could be to calculate RANKX only for the first level of my 3-gerarchy (i.e. on the first Dim I set as the one on the Matrix-Rows section).

Any other suggests?

BR,

M

[Anonymous]

Hi @MattiaMaetini ,

Have you tried the workaround you mentioned? I think it is a step in the right direction. It goes some way to troubleshooting the problem and getting it fixed.

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.