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.
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.
The Query Designer will then generate the DAX code for you.
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.
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.
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.
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.
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.
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.
Then, open the Tablix Properties wizard and change the filter operator to In.
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.
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,