Forum Discussion

cavalierimauro's avatar
cavalierimauro
Icon for Helper I rankHelper I
8 years ago
Solved

How to hide bars when drilling down in the same visualization?

Hi,   I'm trying to understand what would be the better data moder or DAX formula to hide some data when drilling down in the same visualization.   To put it into an example, this is my data tabl...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi cavalierimauro,

     

    Normally power bi not support this, you can refer to below steps to use measure to achieve your requirement.

     

    Steps:

    1. Use L2 to create new table.

    Selector = VALUES('Sample'[L2])

    2. Write measure to check total level and switch display result formula.

    Total = 
IF (
    ISFILTERED ( 'Sample'[L2] ),
    IF (
        SELECTEDVALUE ( 'Sample'[L2] ) IN ALLSELECTED ( Selector[L2] ),
        SUMX (
            FILTER (
                ALL ( 'Sample' ),
                [L1] IN VALUES ( 'Sample'[L1] )
                    && [L2] IN VALUES ( 'Sample'[L2] )
            ),
            [Amount]
        )
    ),
    SUMX ( ALL ( 'Sample' ), [Amount] )
)

    3. Create column chart with L1,L2 and measure, slicer with new table column.

     

    Result:

     

     

    Notice: new table not contains relationship to original one.

     

    Regards,

    Xiaoxin Sheng

cavalierimauro's avatar
cavalierimauro
Icon for Helper I rankHelper I
8 years ago

The expected functionality would be:

 

1) I have this total visualization on the ENTITY_L0 level (total 75)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

2) and when I drill down on it, I have the detail on ENTITY_L1 without the adjustments (total 82)

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi cavalierimauro,

     

    You can add a filter on L1 column to filter item not equal to adjustments.

     

    Regards,

    Xiaoxin Sheng

    • cavalierimauro's avatar
      cavalierimauro
      Icon for Helper I rankHelper I
      8 years ago

      Hi Anonymous, thanks for your answer, I already tried that but this don't solve the problem.

       

      If I add a filter on the L1 column it works fine only when I'm seeing the detail of the element, but when I drill up the filter is still active and it hides the adjustment.

       

      As you can see in the image I attached, the behavior the users want is to:

       

      * have the filter for adjustments activated when I'm seeing the detail (L1 level)

      * but doesn't have the filter activated when I'm seeing the parent (L0 level)

       

      Thanks for your help,

      Mauro

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        HI cavalierimauro,

         

        I'm not so sure why it not available on your side, it works well on my side.

         

        Regards,

        Xiaoxin Sheng