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

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

  • cavalierimauro's avatar
    cavalierimauro
    Helper I
    8 years ago

    That works on the expected way, many thanks for your help!

    Regards