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:

{"Apples","Oranges","Pears"}

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:

image

…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:

image

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:

image

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:

image

You now have a parameter whose suggested values are provided by the output of a query:

image

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

image

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.

image

The last thing to do is to click the Remove Duplicates button so that the list only contains the seven distinct values:

image

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.

18 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.
    Thanks
    Dan

    • 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.

  8. Chris,
    thanks for this post. I was wondering if I can use slicers of underlying queries for a combined query. Do you know if that’s possible? I.e. I got several queries that need to be filtered with individual slicers. All queries are combined in a single query that’s used for a chart. Now I need the slicers of the underlying queries to also effect the chart of the combined query. Any way to pass through the filter? I don’t have any uniques to establish a connection between underlying and combined query (doesn’t make sense to me anyways). And combining all queries in the chart doesn’t work either since I got slicers on the combined query as well. Thanks a million in advance!

    • Hi Rene, are you asking about slicers and how they filter the data in tables in your dataset? Slicers cannot be used to filter the Power Query queries that load data into a dataset.

      • Thanks for your quick reply. Yes, that’s what I was asking about. Bummer. Any other hint for applying different criteria of underlying queries to a combined one?

      • Sure. Let’s say I have tables of a dozen countries. And those tables include several KPIs, like production start of product A or market entry of product B (all in rows as flat table). Now each country’s KPI is only relevant under certain criteria. E.g. in China KPI1 is not relevant when in a certain province and a comment says abc; in Italy all KPIs are relevant; and in the US KPIn is not relevant when the product contains wooden parts. Now I want to create a chart for each KPI but for all countries. I can use a spreadsheet and simply exclude data that doesn’t meet the KPI’s criteria. However, the user is expected to change the criteria over time and I find slicers to be pretty user friendly. So my idea was to create country queries and slicers on those queries for every KPI. And a combined query that puts all the countries’ filtered queries into one decent chart. Thanks again for your efforts!

  9. OK, so based on what you have said here, you should not have individual KPI tables for each country, you should only have one combined table with all relevant KPI data for all countries. As always with Power BI, once you have got the data modelling right everything else becomes easy.

    • Thanks, Chris. That’s what I was going for. Since I named the criteria individually, users can still use slicers to tick filters on or off. Best

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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: