Power BI Blog: Power BI Report Builder – Part 6 (Using Parameters)

Welcome back to this week’s edition of the Power BI blog series. This week, Jonathan Liau takes a look at how to use parameters in the Power BI Report Builder.

Last week, we created a simple report that looked like this:

What if we wanted the report to present data for just two countries, say, Australia and France? Would we have to recreate the query, so that the report’s table and chart just showed those two countries? Can we filter out countries that we do not want to be presented on the report? Fortunately, we do not have to re-write the query. We can use parameters to create filters that act like slicers for our data.

Parameters are created for individual datasets. To create a parameter, we have to right-click on a dataset and select ‘Dataset Properties’:

Clicking on ‘Dataset Properties’ will bring up the ‘Dataset Properties’ dialog.

From here, we have to click on the ‘Query Designer’ option to call the ‘Query Designer’ dialog.

Directing our attention to the top of the window, we can select a Dimension. The Dimension allows us to pick the table that contains the field that we want to filter. For this example, I have selected the Geography table as the dimension and the EnglishCountryRegionName as the Hierarchy. The Equal option has been selected as the Operator and I have ticked all of the countries in the ‘Filter Expression’ option. I have ticked all of the countries in the ‘Filter Expression’ as I want to be able to filter between all of the countries. Before I click OK, I also have to remember to tick the Parameters option and select the ‘Enable Multi Value Parameters’ option (highlighted in a red box in the picture below):

Back at the ‘Dataset Properties’ dialog, we can see that the Query has been updated:

Navigating back to the Power BI Report Builder page. We click on the Run option, to produce the new report. We now have the ability to filter which country we want to see in the report. This is shown by the drop-down option at the top of the report page.

This way we don’t have to go back and adjust the query each time we want to filter the fields in our report.

That’s it for this week. Come back next week for more on the Power BI Report builder.

In the meantime, please remember we offer training in Power BI which you can find out more about here. If you wish to catch up on past articles, you can find all of our past Power BI blogs here.