Forum Discussion

cavalierimauro's avatar
cavalierimauro
Helper 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

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

    • cavalierimauro's avatar
      cavalierimauro
      Helper I
      8 years ago

      Anonymous, in your example that is not working in the desired way.

       

      Following your example, we want that the bar chart visualization show 316 every time, and when you drill down on it, it should show 114, 62 and 109 as in the example.

       

      In other words, we do not want the bar chart at L1 show 285

       

      Let me know if my explanation is not clear for you

      Thanks

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        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