Important Power BI Formulas for Dynamic Filters to Know

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. 

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. 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:

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:

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:

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

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

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