Variables are a symbol of this everchanging and dynamic world. My early memory of such characteristics was the introduction of the alphabets x & y among the numbers in the mathematics class. Since then, we have seen it in various names and forms, but it is commonly known as variables—something which changes, as per requirement and scenarios.
In the world of Power Query & Power BI, we call it Parameters. This article explores one of the primary use of parameters: filtering tables.
The two tables: Manager filters orders & Returns. The filter input for the multiple tables is a parameter named FilterManager.
Setting up the Parameter
Home > Manage Parameters > New Parameter
1. Name: Provide a relevant Name
2. Description (Optional): Provide the description or purpose of the parameter
3. Type: Since the names are text, hence selecting Text
4. Suggested Values:
a. Any Value: Values of the parameters defined by the user’s input.
b. List of Values: Values of the parameters selected by a drop-down selection.
c. Query: Values populates based on values in a query or list
5. Current Value: Input any existing value for the parameter
Click Ok, and the parameter is available under Queries:
Clicking on Manage Parameters brings the previous window.
Using the Parameter as Filter
Click on filter icon of the column > Text Filter > Equals
Note: The filter option may change from Text Filters to any other filter (dependent on the column property, like if the column is a date, then it would be a date filter)
Select Parameter under Filter Rows option & select the parameter created.
Click Ok.
The table filters on the Current Value of the parameter (Sam)
We can use one parameter on multiple tables:
Parameters as List of Values
We can update the Current Value of a parameter using a drop-down.
Select Manage Parameters:
Select List of Values under Suggested Values and update the names in the box below:
We can copy-paste the list from an Excel file as well.
Assign any value under Default Value & Current Value
Click Ok.
Now, we can update the Current Value of the parameter from the list of drop-down
