Creating User Input Variables in a Power BI Report

Requirements: In a report, user should have the flexibility to enter the input number and that number should filter the data. In Power BI reports, we can create slicer with input number and we have options to choose either single select or multi select but we do not have the option to enter the number.

This blog will provide the steps to create user input variable and how it can be used as a filter in the report.

Prerequisites: Power BI Desktop

Sample Employee Details file attached below

1. Open Power BI desktop

2. Click on Get Data, select Excel and Load the employee details file into power bi desktop

3. Create a simple tabular report with all employee details

4. Using slicer visual, we can create either single select or multi select year filter. See the below image

5. But the requirement is to create a single user input parameter which can be used to enter input value like below

To create like this follow below steps

6. In top header ribbon, choose Modeling and click on “What If” parameter

7. New window will open, here we have enter few details like parameter name, data type, minimum and maximum number, increment value and default value. Select add slicer to the page and Click on OK

8. Above step will create new table in the report and will add slicer to the report page. Here we can enter the values with in that range and we can also use the slicer to change the value. We have the option to remove slicer also

9. Now we have the slicer and report but when the slicer value change report will not get affect as there is no relationship between these two

10. Click on “Manage Relationships” in Home ribbon. Add the relationship between these two tables

11. Click on OK. Now report will show data for default value selected. Change the slider on filter or enter value to check the data for that year

12. We can see in the above images that employee details were shown for the selected year and if there is no data for the selected year it will show blank.