topic RANKX LIVE CONNECTION SSAS Tabular 2017 - DRILLDOWN MATRIX without ISINSCOPE DAX FUNCTION AVAILABLE in DAX Commands and Tips

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

MattiaMaetini — Tue, 25 Jun 2024 14:17:23 GMT

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,

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

Anonymous — Wed, 26 Jun 2024 04:18:46 GMT

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.

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

MattiaMaetini — Wed, 26 Jun 2024 13:23:38 GMT

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,

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

Anonymous — Thu, 27 Jun 2024 05:38:06 GMT

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.