How To Create Parameters In Power BI’s Report Builder

by | Power BI

In this tutorial, you’ll learn how to create parameters in Power BI Report Builder. You’ll also learn how to sort your paginated reports using more than one filter.

Parameters allow users to filter report data. They add interactivity in your paginated reports.

In this example, the list is grouped by region. This tutorial will show you how to filter data using one or more parameters for a specific year, month, and/or product.

Adding Parameters In Power BI Report Builder

In the Design view of Report Builder, you can add a parameter by right-clicking on the Parameter folder in the Report Data pane. However, it’s recommended to build your own parameter from your datasets.

Right-click on your Dataset folder and select Add Dataset.

In this example, you’ll be filtering by Year. So write Year as the dataset name and choose the Management Insights report as the data source. Then, click the Query Designer button.

Open the Dates dimension table, look for Year, and pull it into the report. Select the Click to execute the query option.

parameters in Power BI

You’ll then see the three values for the Year without any blanks. If it contains blanks, you need to filter them out or else the query won’t work. Once done, click OK.

parameters in Power BI

The Query Designer will then generate the DAX code for you.

parameters in Power BI

Right-click the Parameter folder and select Add Parameter. This will open the Report Parameter Properties wizard.

Change the Name to “Year” and the Prompt to “Enter Year of choice:”.

In the Available Values tab, select Get values from a query. Then, select Year for all the drop-down boxes.

parameters in Power BI

After you click OK, the parameter you created will appear on the Parameters pane in Report Builder.

Activating The Parameter

Click on your report and select Tablix Properties.

parameters in Power BI

In this example, the header and table are contained in a list. So for every filter you add in your report, all the elements in the list will be affected.

Go to the Filters tab in the Tablix Properties wizard and click Add. In the Expression, choose Dates_Year. For the Value, write [@Year]. This means that the Dates_Year field will be filtered by the Year parameter.

parameters in Power BI

After you press OK, Run the report.

You’ll be asked to choose the filter before you’re able to view the paginated report. Once done, click the View Report button.

You’ll see that the paginated report only contains data that’s within the Year you chose.

Editing Parameters In Power BI Report Builder

You can also edit your parameters by right-clicking them and selecting Parameter Properties.

In the Report Parameter Properties wizard, you can make changes to the General settings and the Available Values. You can also set a default value.

parameters in Power BI

This will automatically show report entries belonging to the default value. In this case, when you Run the report, it will now always show data for the year 2014. You can change this by toggling with the parameter.

Filtering By More Than One Parameter

You can also filter your report using more than one parameter.

All you need to do is choose another data field you want to filter by and repeat the process above. In this example, a new parameter for the Product field is created.

parameters in Power BI

You can also set a default value for the new product parameter. In this case, it’s Product 1.

When you click Run, the report will contain data that corresponds to the default values. Again, you can change this by toggling with the parameters.

If you want to choose more than one filter per parameter, open the Parameter Properties wizard for each parameter and enable the Allow multiple values option.

parameters in Power BI

Then, open the Tablix Properties wizard and change the filter operator to In.

parameters in Power BI

When you Run the report, the edited filter will now have a “Select All” option. You can now choose more than one entry for each parameter.

***** Related Links *****
Power BI Parameters Via Query Editor
M Function For Date Table – How To Add A Parameter In The Query Editor
Power BI Query Parameters: Optimizing Tables

Conclusion

Parameters are important features that any paginated report must have because it streamlines the data depending on the filter you set. This allows end users to easily navigate the report and exclude information that they don’t need.

Creating parameters in Power BI Report Builder is easy to do. And in this tutorial, you learned how to add one or more parameters to your future reports.

All the best,

Sue Bayes

Related Posts