Power BI Filter Measure by Slicer

This Power BI tutorial will help you work with Power BI Filter Measure by Slicer.

Recently, I was working on a Power BI slicer, and my task was to filter a measure using the slicer.

However, one drawback of the Power BI slicer is we can not use measures in the Power BI slicer.

But, here, I will explain how to filter measures by Power BI Slicer.

Power BI Filter Measure by Slicer

In this example, we will see how to filter measures by slicer in Power BI.

Suppose you have a Power BI table named “Sales Data” with three columns: Customer, Sales (2022), and Sales (2023). and you want to compare the sales values this year and the previous year.

To do this, follow the below steps:

1. Open Power BI Desktop. Under the Home tab, click Enter Data.

power bi Filter measure in slicer

2. In the Create Table dialog box, create the below table.

Filter Measure by Slicer in power bi

3. Under the Home tab, click on the New measure.

power bi filter measure based on slicer

4. In the formula bar, use the expression below to compare the sales value. Then click Commit.

Sales Status = 
Var Sales_2022 = SUM('Sales Data'[Sales(2022)])
Var Sales_2023 = SUM('Sales Data'[Sales(2023)])
RETURN
    SWITCH(
        TRUE(),
        Sales_2022 = 0 && Sales_2023 <> 0,"New Customer",
        Sales_2022 < Sales_2023,"Increased Sales",
        Sales_2022 > Sales_2023,"Decreased Sales",
        "No Change")

Where:

  • Sales Status = Name of the Measure
  • Sales Data = Name of the Table
  • Sales(2022),Sales(2023) = Columns Name
  • SWITCH = DAX Function
power bi measure filter based on slicer selection

5. Then you see the Data pane Sales Status created. Under the Visualizations pane, click on the Table visual and drag Customer, Sales(2022), Sales(2023), and Sales Status in the Column field.

slicer measure power bi

Here, we can not add measure to the slicer, but we can do it another way. To do this, follow the below steps.

1. Under the Home tab, click Enter Data.

power bi Filter measure in slicer

2. In the Create Table dialog box, create the table below.

slicer by measure power bi

3. Goto Table view -> select Selection column -> under the Column tools tab, click Sort by column -> select Order.

power bi filter measure based on slicer value

4. Under the Visualizations pane, click on the Slicer visual and drag Selection into Field.

power bi slicer based on measure

When selecting any item in the slicer, you see it not change in the table visual, but we can do it with the help of a filter. To do this, follow the below steps.

1. Under the Home tab, click on the New measure.

power bi filter measure based on slicer

2. In the formula bar, put the below expression. Then click Commit.

Filter Status = 
VAR Current_Selection = SELECTEDVALUE('Sales Class'[Selection])
Var Current_Class = [Sales Status]
RETURN
    SWITCH(
        TRUE(),
        Current_Selection ="New Customer" && Current_Class = "New Customer",1,
        Current_Selection ="Decreased Sales" && Current_Class = "Decreased Sales",1,
        Current_Selection ="Increased Sales" && Current_Class = "Increased Sales",1,
        Current_Selection ="No Change" && Current_Class = "No Change",1,
        Current_Selection ="All Customer",1,
        0
        )
power bi slicer on Filter measure

3. Select the Table visual -> Expand Filters pane -> Drag Filter Status into Filter on this visual -> select Show items when the value is as is -> In the value field, put 1 -> click Apply filter.

filter measure by Power BI slicer

4. Now, the slicer works on the table visually. Lets you want to see only Increased Sales. Click Increased Sales in the slicer.

power bi filter slicer with measure

5. if you want to see All Customer sales status, click All Customer in the Power BI slicer.

Power BI Filter Measure by Slicer

This is how to use this measure in a Power BI slicer.

Conclusion

Now, you have a basic idea of how to use measure in Power BI Slicer. This tutorial taught us how to work with Power BI filter measure by slicer with a simple scenario.

Furthermore, you may like some more Power BI articles: