DAX – Exclude data based on Slicer selection


How works a slicer in Power BI?

A slicer is just like a filter, it is used to narrow a portion of data shown in a visual.
For example if we want to narrow my dataset to only specific regions we just need to tick the region we want to see and all the unticked regions will not show up in our visual.

So a normal slicer includes the selected items and excludes the unselected items. But what if instead of including the selected items we want to exclude them.

How to exclude data from a Slicer selection?

In other words, we want to use a slicer to exclude some data and display all the others.

Step 1:
First, we need to create a disconnected dimension which will be a copy of the dimension used as a slicer, “DimSalesTerritory” in my case.

Step 2:
We then need to create a measure “isExclude” that will be used to find out which rows should be excluded if any.

isExclude = 
IF(
    MAX(DimSalesTerritory[SalesTerritoryRegion]) IN 
    ALLSELECTED('Disconected DimSalesTerritory'[SalesTerritoryRegion]) 
    &amp;&amp; COUNTROWS(ALLSELECTED('Disconected DimSalesTerritory')) <> 
    COUNTROWS(ALL('Disconected DimSalesTerritory')),
    1,
    0
)

The first condition checks which rows should be excluded and the second condition is only used to prevent the measure to exclude everything when nothing is selected.

Step 3:
Now we can create a new measure “Sales Amount (Disconnected)” which will sum up the SalesAmount for all the regions that are not excluded.

Sales Amount (Disconected) = 
VAR FilterRegions = 
    FILTER (
        FactInternetSales,
        [isExclude] =0
    )
RETURN
    CALCULATE (
        sum(FactInternetSales[SalesAmount]), 
        FilterRegions 
    )

Step 4:
All right, you should be all set up now now.
As we can see on the visuals below when we select Australia from the slicer all the other regions appart from Australia are displayed.

Step 4 bis:
If instead of creating a new measure you want the “Exclude Slicer” to work on your visual for any measures you just need to add the “isExclude” measure into the visual filter pane.


Leave a Reply

Your email address will not be published. Required fields are marked *