Filtering Tables Using Parameters: Part 1

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.

Sample Data file

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

Change the parameter value using drop-down

Drop Me a Line, Let Me Know What You Think

+91 9871-641-146