In the previous posts, here and here, I explained how you can use Power BI Desktop Query Parameters for many different use cases. Power BI development team added another cool feature to Power BI Desktop on July 2016 which is the ability to add a List Query output to a query parameter as it’s “Suggested Values” (formerly “Allowed Values”). This feature is very useful and from now on we are not restricted to proviode a static list of values in “Manage Parameters”. In this post I show you how to use a list output in query parameters.

Note: This feature is NOT available in DirectQuery mode at the time of writing this post.

Requirements

In this post as usual I’ll connect to a SQL Server database as a sample. To be able to follow this post you have to have:

1. The latest version of Power BI Desktop (current version is 2.38.4491.282 64-bit (August 2016))
2. AdventureWorksDW

Scenario

In the first post of these series I explained how to create dynamic data sources using Query Parameters. You also learnt how to use Query Parameters in Filter Rows. But, what if we want to filter query results based on the values of a column from a particular table? Previously we couldn’t answer these sort of questions if we want to filter FactInternetSales based on a selected values of EnglishProductName column from DimProductCategories using Query Parameters. But, now we can easily implement those sort of scenarios.

Let’s implement this scenario.

Loading Data into the Model:

• Open Power BI Desktop
• Get data from SQL Server and connect to Adventure Works DW 2016 CTP3
• Select “FactInternetSales”, “DimProduct”, “DimProductSubCategory” and “DimProductCategory” tables then click “Load”

• Switch to “Relationships” view to make sure the relationships detected correctly then click “Edit Queries” from the ribbon

Creating a List Query from a Table Column:

Now we need to create a list from “DimProductCategory” table. To do so:

• In Query Editor window click “DimProductCategory” from Queries pane
• Right click on “EnglishProductCategoryName” and select “Add as New Query”

Note: You can create a list from a column by selecting “Drill Down”. The difference between selecting “Drill  Down” or  “Add as New Query” is that “Drill Down will turn the current query to a list while “Add as New Query” will create a new list query.

Note: You can also use Table.ToList() function in Power Query (M) language to create a list from a table.

Creating a New Query Parameter and Link it to the List Query:

Now it is time to create a query parameter and link to the List Query.

• In Query Editor click “Manage Parameters” from the ribbon
• Click “New”
• Change the name to “Product Category”
• In our sample this parameter is not required so un-tick “Required”
• Change “Type” to Text
• In “Suggested Values” (it used to be Allowed Values) select “Query”
• Select “EnglishProductCategoryName” for “Query”
• Type “Accessories” in “Current Value” then click OK

Reference the Parameter via Filter Rows

Now we need to add a filter to DimProductCategory and reference the parameter. In the first part of these series I explained how to reference a parameter via filter rows so I just quickly go through the steps.

• In Query Editor click “DimProductCategory”
• Add a Text filter to “EnglishProductCategory” column

• Select “Parameter” from filter type then select “Product Category” parameter that we created earlier then click OK

• Click “Close & Apply”

So far we created a parameter on top of a list which we created from a table column.  We also added a row filter to “DimProductCategory”. Now we want to use that parameter in action.

Use the Parameter in Action:

As per the scenario the goal is to filter “FactInternetSales” data using the parameter.  But we added a filter to “DimProductCategory”. The “DimProductCategory” is indeed a master table for “DimproductSubCategory” and the later on is a master table for “DimProduct”. So due to referential integrity when we filter the “DimProductCategory” table it should automatically filter all other detail tabled down to the “FactInternetSales”. Let’s see how it really works in Power BI Desktop.

• Switch to report view
• Put a Matrix on the report page
• Expand “FactInternetSales” then tick “SalesAmount”
• Expand “DimProductCategory” then select “EnglishProductCategory”

As you can see there is a blank item in the Matrix. The reason is because not all rows in the FactInternetSales have a matched row in their master table after we added the row filter which leads them to be shown as blank. This is more sensible if we add “EnglishProductSubCategory” from “DimProductSubCategory” to the Matrix rows.

To overcome this we just need to filter out the blanks from the results.

• In the “Fields” pane scroll down and find “EnglishProductCategoryName” in “Filters”
• Expand “EnglishProductCategoryName” filter
• Change “Filter Type” to “Advanced filtering”
• Select “in not blank” from “Show items when the value:” dropdown list
• Click “Apply filter”

The problem is solved.

Now we can switch the “Product Category” parameter to something else, say “Bikes” and see the results. To do so:

• Click “Edit Queries” from the ribbon then select “Edit Parameters”
• Change the value to “Bikes” then click OK

• Click “Apply Changes”

All done!