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. Set your values 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 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.


1. Set Your Values 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 it 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 Tools22 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.

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


3. Label the User 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 SWITCH

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 he chooses 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 to link your custom tables with the data model. After that, it’s up to you to define the limits of usage for this technique.

Expert Contributors

Built In’s expert contributor network publishes thoughtful, solutions-oriented stories written by innovative tech professionals. It is the tech industry’s definitive destination for sharing compelling, first-person accounts of problem-solving on the road to innovation.

Learn More

Great Companies Need Great People. That's Where We Come In.

Recruit With Us