Data-Driven Power BI Desktop Parameters Using List Queries

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.

9 responses

  1. Pingback: Dew Drop–August 31, 2016 (#2319) - Morning Dew

  2. Pingback: BI-NSIGHT – Power BI (Secure and Audit Power BI, Data Driven Parameters, Snowflake Data Connector) – Excel (Get & Transform Updates / Power Query Updates) – Gilbert Quevauvilliers – BI blog

  3. Too bad cascading parameters doesn’t work (yet?). I have one parameter that specifies the path of a configuration file. Then another parameter where the user has to make a choice based on values out of that configuration file. Doesn’t work.

  4. Pingback: PowerPoint, Auditing, OneDrive and more... - Roundup #59 | Guy in a Cube

  5. Pingback: Power BI Tips & Tricks #11: Updated drill down functionality in Power bi | Tool Shop

  6. Pingback: Datumsdimension in Power BI dynamisch mit M und DAX erzeugen | Linearis :: BI für die Fachabteilung

  7. Chris,
    For some unknown reason, I can’t my #”Fruit Parameter” parameter to create a drop-down listing the contents of list Fruit. Advanced editor – #”Fruit Parameter”:
    “Apples” meta [IsParameterQuery=true, ExpressionIdentifier=Fruit, Type=”Text”, IsParameterQueryRequired=true]. #”Fruit” query: {“Apples”,”Oranges”,”Pears”}

    Please post your M code of your Fruit Parameter, so I can compare.

    • There’s a bug in Power BI Desktop that means you won’t see the drop down in the Query Editor. You will see it if you close the Query Editor, go to the main Power BI window and click Edit Queries/Edit Parameters

      • Thanks for the tip. I don’t think Excel has the same Edit Parameters command/object on it’s main window ribbon. Have you ever gotten a list query reference in a parameter to work in Excel? I haven’t.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: