Mastering Dynamic Filtering in Power BI

Welcome to this comprehensive guide on how to filter based on a categorical measure in Power BI. In this post, we will explore how to create a dynamic filtering mechanism that allows users to slice their data based on specific measure evaluations. This method is particularly useful when working with sales data across different years, as we will demonstrate through a practical example.

Understanding Categorical Measures

To kick things off, let’s clarify what we mean by a categorical measure. In the context of Power BI, a categorical measure evaluates data and categorizes it into distinct groups based on defined criteria. For instance, if we have sales data from 2020 and 2021, we can create a measure that categorizes customers based on whether their sales increased, decreased, remained the same, or if they are new customers.

In our example, we have a measure called Sales Class. This measure evaluates customer sales from 2020 and 2021 and assigns a category based on the following logic:

• Increased Sales: If sales in 2021 are greater than in 2020.
• Decreased Sales: If sales in 2021 are less than in 2020.
• No Change: If sales are the same in both years.
• New Customer: If there were sales in 2021 but none in 2020.

This categorization allows users to filter their reports dynamically based on these categories, making it easier to analyze trends and customer behaviors over time.

Creating the Sales Class Measure

Let’s dive into the setup process. We will create a new measure called Sales Class. First, we need to establish variables for the sales data from both years. Here’s how we do it:

Sales Class = 
VAR Sales2020 = SUM('Sales'[Sales2020])
VAR Sales2021 = SUM('Sales'[Sales2021])
RETURN
SWITCH(TRUE(),
    Sales2021 = 0 && Sales2020 <> 0, "New Customer",
    Sales2021 > Sales2020, "Increased Sales",
    Sales2021 < Sales2020, "Decreased Sales",
    "No Change"
)

With this DAX formula, we are evaluating sales for each customer and categorizing them accordingly. Once we have this measure in place, we can visualize it in a table to see how our customers are classified.

Visualizing the Data

Next, we need to visualize our data for better insights. We will create a table that includes customer names, their sales for 2020 and 2021, and our newly created Sales Class measure. This gives us a clear view of how each customer is performing across the two years.

Setting Up the Slicer

Now that we have our measure and table set up, it’s time to create a slicer that allows users to filter the table based on the Sales Class measure. Here’s how we can do that:

1. Create a new table to hold our slicer values. We will call this Sales Class Table and include the categories we defined earlier.
2. Define an order for the slicer values to ensure they appear in a logical sequence.
3. Load this table into Power BI and create a slicer visual using the Selection column from our new table.

After setting up the slicer, we will format it to allow single selection and adjust its appearance for better usability.

Implementing Filter Logic

At this point, the slicer is functional, but it doesn’t yet filter the table. To achieve this, we need to create another measure called Filter Status that will control the visibility of table rows based on the slicer selection. Here’s how we can set it up:

Filter Status = 
VAR CurrentSelection = SELECTEDVALUE('Sales Class Table'[Selection])
VAR CurrentClass = [Sales Class]
RETURN
SWITCH(TRUE(),
    CurrentSelection = "Increased Sales" && CurrentClass = "Increased Sales", 1,
    CurrentSelection = "Decreased Sales" && CurrentClass = "Decreased Sales", 1,
    CurrentSelection = "New Customer" && CurrentClass = "New Customer", 1,
    CurrentSelection = "No Change" && CurrentClass = "No Change", 1,
    CurrentSelection = "All Customers", 1,
    0
)

This measure evaluates the current selection from the slicer against the Sales Class measure for each customer. If they match, it returns a value of 1, allowing the table to show that customer; otherwise, it returns 0.

Applying the Filter to the Table

Now that we have our Filter Status measure, we can apply it to the table visual. Here’s how:

1. Open the filters pane for the table visual.
2. Add the Filter Status measure to the Visual Level Filters section.
3. Set the filter condition to show only rows where Filter Status equals 1.

With this filter in place, selecting a category in the slicer will dynamically filter the table to show only the relevant customers.

Dynamic Filtering in Action

Now that we have everything set up, let’s see the dynamic filtering in action. When we select different categories in the slicer, the table updates accordingly, displaying only the customers that fall under the selected Sales Class.

This dynamic behavior enhances the user experience, allowing for more focused and insightful analysis of customer sales trends.

Conclusion

In this guide, we’ve explored how to filter data based on a categorical measure in Power BI. By creating a Sales Class measure, setting up a slicer, and implementing filter logic, we have empowered users to analyze customer data dynamically. This method not only improves the usability of reports but also allows for deeper insights into sales performance over time.

For further learning, consider checking out additional resources on Power BI and DAX, as well as our on-demand training courses