In Power BI the slicers allow us to filter and view only the information that we want but we can also in Power BI Exclude data based on Slicer selection.

In this short post, I will show how we can reverse the behaviour of a slicer “excluding” the selected values instead of “filtering” the selected values.

What is a Power BI Slicer?

In Power BI a Slicer is a canvas visual that allows us user to filter and view only the information that we want.
So slicers are used to narrow the portion of the data that we want to analyse in the other visuals.

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 our 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.

When do we need to reverse a slicer’s behaviour?

The most common scenarios where we want to reverse the slicers’ behaviours in Power BI (Exclude value via a slicer) are when we need to remove known outliers.

In fact, outliers can have significant impact on the mean which can result in misleading interpretations.

For example, if we want to do an average comparison across each product, we may need to exclude a product which is selling 20 times more than the average of the other products.

Another example is when we want to forecast sales based on the last 3 years sales.
However, we know that the sales for a specific month were extremely lower than the other months so then we decide to exclude it from the sales history to keep our sales forecasting more accurate.

Power BI Exclude data based on 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]) 
    && 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.