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:
[sourcecode language=”text” padlinenumbers=”true”]
{"Apples","Oranges","Pears"}
[/sourcecode]
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.
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.
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.
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?
I’m not quite sure what you are trying to do here – can you give me a more detailed description of the problem?
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!
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
HI Is this feature available for DIrect Query COnnection?
I don’t know – I suggest you try it!
Hi Chris,
We are building a new analytics platform using power bi Embedded. We have been successful in passing the parameters sql query and getting the results. But we want to be able to dynamically pass the parameters based on the the user login(through RLs). This is proving to be a problem. Any suggestions?
I don’t think this is possible, sorry. Power BI parameters are not something that can be changed by an end user or set differently for different end users.
IS it possible with dates, how we can use latest file date to filter data table in parameter?
Hi Chris, going back to your original Apples, Oranges, Pears example, how do you filter the main query table using all three of these values? I can only get it to do one at a time.
(I’m trying to filter my table by a sub-list of values, e.g. trying to pull apples, oranges and pears out of a list that contains apples, bananas, oranges, kiwis, pears, cherries, melons).
Matt
Hi Chris, I am new in Power Bi but using Crystal, SSRS, SQL for many years. My question is possible in Power BI to create a page with parameters only and depending of the selection on the parameters get only the data selected like in SSRS. For example I have data with 2 fields Country and population.
The first page just have a parameter Country that when I select one or more countries then the other pages just display visualizations only for the countries selected.?