How to Show Active Filters in Power BI Using DAX (One Method Among Many)
Table of contents
- Overview of the Power BI Setup
- Step-by-Step Guide to Display Active Filters
- Step 1: Define Variables for Filtered Items
- Step 2: Check Which Filters Are Applied
- Step 3: Count the Selected Filters
- Step 4: Build the Filters Display String
- Step 5: Limit the Number of Filters Displayed
- Step 6: Returning the Active Filters
- Conclusion
Power BI reports often require users to apply various filters to explore the data in specific ways. But once those filters are applied, it’s helpful to show the active filters on the report, allowing users to understand exactly what data they’re seeing. This tutorial will guide you on how to display active filters dynamically using DAX.
Overview of the Power BI Setup
In this example, we are using a report with filters applied to Year, Category, and Product. Here’s a snapshot of the report layout:
Year: Filtered between 1996 and 1997.
Category: Filters include "Beverages" and "Condiments."
Product: "Aniseed Syrup" is selected.
Below the slicers, there’s a Sales by Month line chart that updates dynamically based on the filters applied. To enhance the user experience, we will show the applied filters in a text box beneath the chart.
Step-by-Step Guide to Display Active Filters
Step 1: Define Variables for Filtered Items
We start by defining variables in DAX for each filter, such as Year, Category, and Product. This will capture the values selected by the user in the report.
VAR _year = "Year (" & CONCATENATEX(VALUES('Calendar'[Year]), 'Calendar'[Year], ", ") & ")"
VAR _category = "Category (" & CONCATENATEX(VALUES(Categories[CategoryName]), Categories[CategoryName], ", ") & ")"
VAR _product = "Product (" & CONCATENATEX(VALUES(Products[ProductName]), Products[ProductName], ", ") & ")"
In the formula:
CONCATENATEX
is used to concatenate the values for each filter type. For instance, if multiple categories or years are selected, they will appear in a comma-separated list.
The output might look something like this:
Year (1996, 1997)
Category (Beverages, Condiments)
Product (Aniseed Syrup)
Step 2: Check Which Filters Are Applied
Next, we need to determine if any filter has been applied for each category (Year, Category, Product). We use the ISFILTERED
function to check this:
VAR _yearFiltered = ISFILTERED('Calendar'[Year])
VAR _categoryFiltered = ISFILTERED(Categories[CategoryName])
VAR _productFiltered = ISFILTERED(Products[ProductName])
This part of the DAX formula returns TRUE
if the corresponding field is filtered and FALSE
if it is not.
Step 3: Count the Selected Filters
To ensure proper handling of multiple selections, we count the number of items selected for each filter. This will help later when displaying active filters in the report.
VAR _totalFiltersSelected =
IF(_yearFiltered, COUNTROWS(VALUES('Calendar'[Year]))) +
IF(_categoryFiltered, COUNTROWS(VALUES(Categories[CategoryName]))) +
IF(_productFiltered, COUNTROWS(VALUES(Products[ProductName])))
Step 4: Build the Filters Display String
We now construct a display string that shows the active filters. If no filters are applied, we will return “No selection.” Here’s how we do this in DAX:
VAR _filters = IF(_yearFiltered || _categoryFiltered || _productFiltered,
IF(_yearFiltered, _year & " ") &
IF(_categoryFiltered, _category & " ") &
IF(_productFiltered, _product & " "),
"No selection")
The IF
statements check whether a filter is applied and, if true, concatenate the selected values into the final string.
Step 5: Limit the Number of Filters Displayed
If many filters are applied, the display can get cluttered. To avoid this, we set a maximum limit of 5 filters to be shown. If the number of filters exceeds this limit, we display a summary instead.
VAR _maxFilterDisplay = 5
VAR _filtersMaxDisplay = IF(_totalFiltersSelected <= _maxFilterDisplay, _filters, "Multiple selections (" & _totalFiltersSelected & " active filters)")
In this part, the formula limits the number of visible filters and replaces the details with a message such as "Multiple selections (5 active filters)" when necessary.
Step 6: Returning the Active Filters
Finally, we return the concatenated active filters in the format we want:
RETURN
"Active filter(s): " & _filtersMaxDisplay
This string will appear in your Power BI report, showing which filters are currently applied in a user-friendly manner.
Full Code
Active Filters =
VAR _year = "Year (" & CONCATENATEX(VALUES('Calendar'[Year]), 'Calendar'[Year], ", ") & ")"
VAR _category = "Category (" & CONCATENATEX(VALUES(Categories[CategoryName]), Categories[CategoryName], ", ") & ")"
VAR _product = "Product (" & CONCATENATEX(VALUES(Products[ProductName]), Products[ProductName], ", ") & ")"
VAR _yearFiltered = ISFILTERED('Calendar'[Year])
VAR _categoryFiltered = ISFILTERED(Categories[CategoryName])
VAR _productFiltered = ISFILTERED(Products[ProductName])
VAR _maxFilterDisplay = 5
VAR _totalFiltersSelected =
IF(_yearFiltered, COUNTROWS(VALUES('Calendar'[Year]))) +
IF(_categoryFiltered, COUNTROWS(VALUES(Categories[CategoryName]))) +
IF(_productFiltered, COUNTROWS(VALUES(Products[ProductName])))
VAR _filters = IF(
_yearFiltered || _categoryFiltered || _productFiltered,
IF(_yearFiltered, _year & " ") &
IF(_categoryFiltered, _category & " ") &
IF(_productFiltered, _product & " "),
"No selection"
)
VAR _filtersMaxDisplay = IF(
_totalFiltersSelected <= _maxFilterDisplay,
_filters,
"Multiple selections (" & _totalFiltersSelected & " active filters)"
)
RETURN
"Active filter(s): " & _filtersMaxDisplay
Conclusion
Displaying active filters in Power BI using DAX enhances the user experience by providing clear visibility into the selected filters. It ensures transparency and context, making reports easier to interpret. Follow the steps outlined above to implement this feature in your reports and give users more control and understanding of the data they’re viewing.
By dynamically showing the filters applied, users can instantly see how their selections affect the visualizations, leading to better data exploration and insights.