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

[cavalierimauro]

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

I want to have a functionality of drilling down from ENTITY_L0 to ENTITY_L1, but:

* If I am in the ENTITY_L0 level, I want to see the whole total (total would be 75)

* But if I go drilling down to the ENTITY_L1 level, I only want to see the data by entity, but without show the one called "adjustment" (sum of the entities would be 75)

How is possible to have this working ? I can change the shape of the data coming into PowerBI if needed.

Explaining about the business need, we need a hierarchy of entities, that doesn't sum up the same when you go the the parent versus the childrens (they have some adjustments in between that the users doesn't want to be show in the reports)

Thanks for your comments,

[cavalierimauro]

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)

[v-shex-msft]

Hi @cavalierimauro,

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

Regards,

Xiaoxin Sheng

[cavalierimauro]

Hi @v-shex-msft, 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

[v-shex-msft]

HI @cavalierimauro,

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

Regards,

Xiaoxin Sheng

[cavalierimauro]

@v-shex-msft, 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

[v-shex-msft · Accepted solution]

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]

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

Regards