The July 2016 update for Power BI Desktop included the ability to make parameters data-driven, by giving the option to bind the Suggested Values (previously called the Allowed Values) property of a parameter to the output of a query that returns a list. However that’s pretty much all the information blog post gives you, so if you’re wondering what a list is and how to get query to return one so you can use it in a parameter then read on…
A list is one of the most useful data types in M, the language behind all of Power BI Desktop’s data-loading functionality. A list is nothing more than an ordered list of values of any data type and it’s written in M as a comma-delimited list enclosed in braces. Any query can return a list – most queries you build in the Query Editor window in Power BI Desktop will return tables but a query can in fact return a value of any data type.
Here’s an example of how to define a list containing three values in M:
You can create a query that returns this list in Power BI Desktop by clicking on the Get Data button and selecting the New Blank Query option:
…and then, in the Query Editor window, opening the Advanced Editor, deleting all the code in there and replacing it with an expression like the one above that returns a list:
When you click Done the Advanced Editor dialog will close and you’ll see the values in the list displayed in the Query Editor, along with the List Tools tab on the ribbon above it:
Although this looks table-like, it isn’t a table – don’t get confused between tables and lists!
Now you have a query that returns a list you can create a new parameter that uses it. In the Parameters dialog, create a new parameter then choose Query in the Suggested Values dropdown box and then choose the name of the query that returns the list (in this case I’ve called the query Fruit) in the Query dropdown box:
You now have a parameter whose suggested values are provided by the output of a query:
OK, so now you know what a query that returns a list looks like and how to use it in a parameter. The example above isn’t very practical though – how do you get a list of values from a real-world query? It’s actually very easy.
Imagine you have a query returning the contents of the DimDate table from the Adventure Works DW SQL Server sample database and you want to create a parameter that allows your user to select a day of the week. The EnglishDayNameOfWeek column in the DimDate table contains the day names that you need.
To get a list from a column in a table you need to click on the column to select it and then right-click and select either:
Drill Down
Add as New Query
Drill Down creates a new step in the current query that returns a list of all of the values in the selected table; Add as New Query gives you the same list but as a new query.
The last thing to do is to click the Remove Duplicates button so that the list only contains the seven distinct values:
Now you have a list whose values are derived from an external data source, ready to create a data-driven parameter.
One final thing to note: you may be wondering if it’s possible to create cascading parameters (like in Reporting Services) where the selection made in one parameter controls the available values for another parameter. As far as I can see this isn’t supported yet, unfortunately.