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 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

Need Help

3 Replies

  • 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.

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

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        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.