Introduction to Parameters in Power BI Desktop
Parameters in Power BI provides the flexibility to change the output of queries depending on their value dynamically.
Parameters basically is used for changing the argument values for particular transforms and data source functions and inputs in custom functions.
As you can see, there are two fields Category and Sub-Category displaying in table visual that using Gobal Super Store dataset.
Following steps are involved in implementing a Parameters in Power BI Desktop.
Creating a Parameter using a List of values
Lets create a parameter list for category and filter the data based on selected category value in parameter list.
Go to Power Query Editor, then click on Manage Parameters tab in Ribbon bar and select New Parameter from context menu as shown in below screenshot.
After clicking on New Parameters a Manage Parameters dialog box opens as shown below.
Lets provide the values for fields as following:
- Name: provide a name for the parameter.
- Description: provide a short description about parameter that defines the purpose of parameter, it is displayed next to the parameter name when parameter information is displayed.
- Required: this checkbox specifies whether users can specify a value for the parameter need to be provided.
- Type: it applies the data type restriction to the input value for the parameter.
- Suggested values: it provides the user with suggestions to select a value for the Current Value from available values Any values, List of values, Query.
- Default value: you can provide the default value for a parameter(Optional).
- Current value: you can provide the parameter’s value for the current report.
After providing the required details, click on OK button. You will see that parameter is created as shown below.
After that go to the View tab in the Power Query Editor, and enable the Always allow option in the Parameters group as shown below.
Now in data model click on category column after that select a Text filters then select Equals.. from context menu as shown below.
After clicking on Equals.., a Filter Rows dialog box opens there you can specified a parameter that you created. Select a Parameter from dropdown shown below.
Once you select a parameter you will see that in dropdown a parameter Param_Category that you created is selected automatically. After that click on Ok Button.
Once you click on OK button, you will see your dataset is get filtered automatically for Parameter’s current value that you set as “Office Supplies”.
Lets save the changes and close the Power Query Editor, click on Close & Apply button.
Once you save the changes, you will see that data also get filtered in table visuals for “Office Supplies”.
Lets filter the data using parameters, Click on Transform Data, select Edit Parameters .
Once you click on Edit parameter, a Edit parameters dialog box opens.
Here you can select a values from list of values, Lets select “Technology” and click on OK button.
Then allow Apply Changes.
And you can see, data is filtered for Technology.
Creating a Parameter using a List Query
You can also use List Query to provide the list of suggested values that you can later select for the Current Value in parameter.
As we have just created a parameter on category to filter the data based on parameter value, so first you need to remove the applied filter from dataset, just remove the step filtered rows from data model as shown below.
Now you need to create List table that will have category name list, and this List table will be used in query list.
Go to Power Query Editor, click on Category column in dataset and select Add as New Query.
After clicking on Add as New Query, a List table named as Category is created as shown below.
Now remove the duplicate category values from list table, right click on column List and select Remove Duplicates.
Now you will see, that list table having a unique category values.
Now we will modify the Function Param_Category for providing suggested values using Query List.
Click on Parameter named Param_Category as you can see on the left side in Power Query Editor window, then select Manage Parameter to edit the Parameter.
After clicking on Manage Parameter, Manage Parameters dialog box opens, provide the values shown below.
This time for suggested value element, we have selected Query and in Query element a list table named category.
After providing details, just click on OK button.
Now we will set up to filter the rows based on parameter values. This is the same step that we did earlier.
Once you click on OK Button, then Just save you changes by clicking on Close & Apply button.
After saving the changes, Lets filter the data using parameter.
This time, lets filter the data for “Furniture”, Just click on Transform Data, select Edit Parameters.
And select the “Furniture” from dropdown lists and click on OK button.
And you will see, data is filtered in table visual for category “Furniture”.
Also Read..
Change the Data Source dynamically in Power BI using Parameters
