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:
- The latest version of Power BI Desktop (current version is 2.38.4491.282 64-bit (August 2016))
- 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
Continue reading “Power BI Desktop Query Parameters, Part 3, List Output”