Forum Discussion
Helper IRANKX LIVE CONNECTION SSAS Tabular 2017 - DRILLDOWN MATRIX without ISINSCOPE DAX FUNCTION AVAILABLE
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.
Helper ITks 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
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.