Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

ISINSCOPE Hide Empty Rows in a Matrix Hierarchies

Hi Guys;

 

I need some  Help, I have a matrix with 7 Hierarchies Levels, and Total Quantity field, in the level 4, 5, 6 and 7, I have Rows Empty. The situation currently I only can remove the rows in the level 4 without affect the Total Quantity, but is there some way to use isinscope in the same measure for all levels (5,6,7) with rows empty?

 

This is my code for the Level 4:

Rows Blank = SWITCH(
    TRUE(),
    AND(
        ISINSCOPE(FACT_SOLPED_CON_EST_LIB[Desc. Sociedad N4]),
        ISBLANK(VALUES(FACT_SOLPED_CON_EST_LIB[Desc. Sociedad N4]))
    ),BLANK(),
    SUM(FACT_SOLPED_CON_EST_LIB[Cant. Sol Ped])
    )

This is my result inthe level 4, the Matrix works perfect!

 

 

But to add the next Level don´t works:

 

Regards!

Need Help

4 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi Anonymous 

    I try to produce your problem.

    First, i want to confirm something:

    In data below, when showing level1~level7, the second row should not show on the matrix, right?

    If my example is not correct, could you show me a simple one?

    level1 level2 level3 level4 level5 level6 level7 value
    A AB ABC ABCD ABCDE ABCDEF ABCDEFG 1
    A AB ABC ABCD ABCDE ABCDEF ABCDEFH  

     

    Meanwhile, you could learn How to use ISINSCOPE in Hierarchies from the following video.

    DAX for Power BI - Determining Hierarchy Level (ISINSCOPE)

    If you still have problems, pleas let me know.

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Maggie;

      Thanks for the video was so helpful,  this is a complete example thaT I need to do:


      Data Base

       

      The Idea is make a visual Matrix and must work in this way:
      Don´t show empty rows.
      Respect the value and content in before levels
      Respect the Total Value always

       

      This is the way to show the Matrix in each change of hierarchies:

       

      Im tryng whit this code but don´t work all well:

      Medida = 
IF (
    SWITCH (
        TRUE (),
        AND ( ISINSCOPE ( Hoja6[Level 1] ), ISBLANK ( VALUES ( Hoja6[Level 1] ) ) ), BLANK (),
        SUM ( Hoja6[Value] )
    ),
    IF (
        SWITCH (
            TRUE (),
            AND ( ISINSCOPE ( Hoja6[Level 2] ), ISBLANK ( VALUES ( Hoja6[Level 2] ) ) ), BLANK (),
            SUM ( Hoja6[Value] )
        ),
        IF (
            SWITCH (
                TRUE (),
                AND ( ISINSCOPE ( Hoja6[Level 3] ), ISBLANK ( VALUES ( Hoja6[Level 3] ) ) ), BLANK (),
                SUM ( Hoja6[Value] )
            ),
            IF (
                SWITCH (
                    TRUE (),
                    AND ( ISINSCOPE ( Hoja6[Level 4] ), ISBLANK ( VALUES ( Hoja6[Level 4] ) ) ), BLANK (),
                    SUM ( Hoja6[Value] )
                ),
                IF (
                    SWITCH (
                        TRUE (),
                        AND ( ISINSCOPE ( Hoja6[Level 5] ), ISBLANK ( VALUES ( Hoja6[Level 5] ) ) ), BLANK (),
                        SUM ( Hoja6[Value] )
                    ),
                    IF (
                        SWITCH (
                            TRUE (),
                            AND ( ISINSCOPE ( Hoja6[Level 6] ), ISBLANK ( VALUES ( Hoja6[Level 6] ) ) ), BLANK (),
                            SUM ( Hoja6[Value] )
                        ),
                        IF (
                            SWITCH (
                                TRUE (),
                                AND ( ISINSCOPE ( Hoja6[Level 7] ), ISBLANK ( VALUES ( Hoja6[Level 7] ) ) ), BLANK (),
                                SUM ( Hoja6[Value] )
                            ),
                            SWITCH (
                                TRUE (),
                                AND ( ISINSCOPE ( Hoja6[Level 7] ), ISBLANK ( VALUES ( Hoja6[Level 7] ) ) ), BLANK (),
                                SUM ( Hoja6[Value] )
                            )
                        )
                    )
                )
            )
        )
    )
)

      In advance thanks for your time!

       

      • v-juanli-msft's avatar
        v-juanli-msft
        Community Support
        7 years ago

        Hi Anonymous 

        As tested, it seems impossible to hide the blank rows on "Row header".

         

        Best Regards
        Maggie

         

        Community Support Team _ Maggie Li
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.