Important Power BI Formulas for Dynamic Filters to Know

Dynamic filtering allows Power BI users to customize how data is displayed and interact with the data without using bookmarks or buttons. Follow these steps to create a dynamic filter in Power BI.

Written by Nikola Ilic
Woman at computer creating data graphics representing power bi
Image: Shutterstock / Built In
Brand Studio Logo
UPDATED BY
Matthew Urwin | Apr 18, 2025
Summary: Power BI is a Microsoft platform that provides data visualization and analysis tools. Dynamic filters allow users to customize what data they view in Power BI, so they can interact with data without relying on buttons, bookmarks and other features. Creating these filters is an easy process.

I recently received a request to deliver a report that would allow end users to choose to see data filtered by a specific year or month, or as a year-to-date (YTD) calculation. 

How to Create a Dynamic Filter in Power BI

  1. Develop measures for your Power BI dynamic filter.
  2. Create a new table in Power BI to hold data for the dynamic filter.
  3. Label the user’s selected display.
  4. Put the user’s selection into context with the SWITCH function.
  5. Create a filter for the user’s selection.

The first thing that came to my mind was to play around with buttons, actions and bookmarks, but I decided to apply a different approach to tackle this using dynamic filtering in Microsoft Power BI

I’ll use the Contoso database for this example.

 

A guide to creating dynamic filters in Power BI. | Video: Data Mozart

1. Develop Measures for Your Power BI Dynamic Filter

The first step is to create a measure for sales amount:

Sales Amt = SUM('Online Sales'[SalesAmount])

I also need a measure that will calculate year-to-date sales amount (YTD), which we will create as:

Sales Amt YTD = CALCULATE('Online Sales'[Sales Amt],DATESYTD(Dates[Date]))

We are simply using the built-in data analysis expression (DAX) function DATESYTD, which will automatically evaluate an expression and return the desired values. That way, when I drag a table to the Power BI desktop canvas and select the year 2009, I’m going to get the following numbers for our newly created measures:

Table containing data for sales amount and sales amount year to date by month
Table in Power BI representing sales amount and sales amount year-to-date. | Image: Nikola Ilic

The “Sales Amt” column shows figures for every selected month, while “Sales Amt YTD” adds those figures to display YTD values.

Check Out These BI Tools16 Business Intelligence Tools to Know

 

2. Create a Table in Power BI to Hold Data for the Dynamic Filter

Now, I need to find a way to enable users to see one of the specific values based on their selection. While this can be achieved using buttons and bookmarks and hiding and showing visuals based on the user’s selection, let’s try a slightly different approach.

First, we’ll create a new table, which will hold data for our dynamic filter:

Create a table display in power bi, with calculation timeframe circled
Creating a new table in Power BI to hold data for the dynamic filter. | Image: Nikola Ilic

Under “Enter Data,” I’ve just created a simple table called “Calculation TimeFrame” with two columns: “ID” and “TimeFrame.” Of course, you can define as many options as you want depending on your needs. After I load this table to a model, I need to find a way to somehow connect it with my existing model.

 

3. Label the User’s Selected Display

Next, I need to know what the user selected to display. Therefore, the following measure needs to be created:

Selected TimeFrame = MIN('Calculation TimeFrame'[ID])

This measure will return the minimum ID value of user selection. In case no value is selected, the option with ID = 1 (Monthly) will be displayed.

 

4. Put the User’s Selection Into Context With the SWITCH Function

The next step is the most interesting since it puts the user’s selection into the context of the existing data model. Within the online sales table, I’ll create the following measure:

Sales Amt Selected = SWITCH([Selected TimeFrame],
                       1,'Online Sales'[Sales Amt],
                       2,'Online Sales'[Sales Amt YTD])

This measure takes the ID value from user selection, and based on that value, displays the respective calculation. It can easily be done with the SWITCH function. With this function, if the user chooses ID 1 (Monthly), it’ll return the “Sales Amt” value. If they choose ID 2 (YTD), it’ll return the YTD value. Simple as that.

More Data VisualizationUsing T-SNE in Python to Visualize High-Dimensional Data Sets

 

5. Create a Filter for the User’s Selection

The final touch is to create a filter for this. Simply drag the slicer visual and enter the time frame field. Make that horizontal, so it looks a bit nicer:

Power BI screen to create a filter for the time frame
Dragging the slicer visual in Power BI to create a filter for the user’s selection. | Image: Nikola Ilic

As you can see, the default monthly time frame will be displayed:

default monthly time frame display in power bi
Default monthly time frame display for sales amount in Power BI. | Image: Nikola Ilic

However, if you click on the YTD slicer button, the visuals will perform differently:

Increasing graph of year-to-date sales amount data in power bi
Sales amount filtered by year-to-date in Power BI. | Image: Nikola Ilic

Now, visuals show YTD values.

 

Advantages of Using Dynamic Filtering in Power BI

This is a neat trick to avoid using buttons and bookmarks for your users’ requests while still keeping everything tidy. It’s important to define proper measures and link your custom tables with the data model. After that, it’s up to you to define the limits of usage for this technique.

Frequently Asked Questions

Power BI is a platform that uses software services, applications and connectors to support data visualization and analysis services. It’s designed for businesses looking to visualize larger amounts of data.

Dynamic filtering refers to customizing how data is displayed based on certain variables. This enables Power BI users to interact with data without having to rely on bookmarks, buttons and other features.

In Power BI, the SWITCH function evaluates an expression, compares it to a list of values and returns the first result that matches. This function simplifies the process of writing conditional statements.

Explore Job Matches.