Even though the documentation for dynamic M parameters does mention how to handle multi-select in the M code for your Power Query queries, I thought it would be useful to provide a detailed example of how to do this and explain what happens behind the scenes when you use multi-select.
Once again I’m going to use Azure Data Explorer as my DirectQuery data source in this post. Consider the following simple KQL query, which returns a list of counties in the UK and the average price paid for real estate in them:
pricepaid
| where county in ('DEVON', 'CORNWALL', 'KENT')
| summarize avg(price) by county
The third line of this query uses a combination of the KQL where and in operators to filter the list of counties in a way that’s very similar to other query languages such as SQL. The challenge is to write an M expression that will generate the comma-delimited list of county names in parantheses at the end of this line.
Taking the query above and generating a non-dynamic DirectQuery table in Power BI is straightforward, and let’s assume that you have already created an M parameter called Selected County:
… a table of county names:
…and bound the County column of this table to the SelectedCounty M parameter and turned on the Multi-select option in Model View in the main Power BI Desktop window:
Now the confusing thing for me, when I first started to look at this problem, was that the M parameter called SelectedCounty I created was of type Text but the example code in the documentation was written for an M parameter of type list – when in fact it isn’t possible to create an M parameter of type list in the Power Query Editor (at least not at the time of writing). It turns out that when the Multi-select option is turned on Power BI is able to send a value of type list to the parameter regardless of what type you have defined for it.
With that knowledge here’s an example of an M query to generate the query using the parameter:
let
CountyList =
if
//check to see if the parameter is a list
Type.Is(
Value.Type(SelectedCounty),
List.Type
) then
//if it is a list
let
//add single quotes around each value in the list
AddSingleQuotes = List.Transform(
SelectedCounty,
each "'" & _ & "'"
),
//then turn it into a comma-delimited list
DelimitedList = Text.Combine(
AddSingleQuotes,
","
)
in
DelimitedList
else
//if the parameter isn't a list
//just add single quotes around the parameter value
"'" & SelectedCounty & "'",
//generate and run the KQL query
Source = AzureDataExplorer.Contents(
"https://mycluster.northeurope.kusto.windows.net",
"pricepaid",
"pricepaid#(lf)
| where county in
("
& CountyList &
")#(lf)
| summarize avg(price) by county",
[
MaxRows = null,
MaxSize = null,
NoTruncate = null,
AdditionalSetStatements = null
]
)
in
Source
Note that the CountyList step has to check the data type of the parameter using an if statement, because in the Query Editor it will always be Text whereas in the report it will be a list if multi-select is turned on and Text if not.
Here’s the final report showing a multiselect slicer passing values into this query:
[Thanks to Ravi Kiran Vemulapalli and Sujata Narayana for their help with this post]
One thing worth mentioning – the whole point of having dynamic M parameters in the ADX connector, was to support generating efficient KQL queries. In your sample, this means performing the `where` statement BEFORE the `summarize` statement. While in this simple sample, ADX is smart enough to propagate the filtering before the aggregation when executing the query, on more complex queries this might not always be the case…
Thanks for pointing that out – I’ve updated the post now
Why the ADX data source is notified as not supported one? the query was working one day and stopped working another day? Is thsi available in power bi service?
Hi, is there a way to use the Select All-Option?
The Multi-Select is working perfectly fine, but the Select All is greyed out.
If I select nothing, the label in the dropdown is “All”, but only seems to use the Value that is set in the parameter in Power Query.
We use the parameters combined with Kusto Queries. The values for the button are queried from our ADX.
Hi all, I would like o achieve this for Oracle DB query. Unfortunately I am getting error as below. May I kindly ask you advice how can I resolve?
“DataSource.Error: Oracle: ORA-00933: SQL command not properly ended
Details:
DataSourceKind=Oracle
DataSourcePath=xxxx
Message=ORA-00933: SQL command not properly ended
ErrorCode=-2147467259”
Query:
let
PAM_LIST =
if
//check to see if the parameter is a list
Type.Is(
Value.Type(PAM),
List.Type
) then
//if it is a list
let
//add single quotes around each value in the list
AddSingleQuotes = List.Transform(
PAM,
each “‘” & _ & “‘”
),
//then turn it into a comma-delimited list
DelimitedList = Text.Combine(
AddSingleQuotes,
“,”
)
in
DelimitedList
else
“‘”&PAM&”‘”,
Source = Oracle.Database(“xxxxx”, [HierarchicalNavigation=true, Query=”select *#(lf)from join xxx #(lf) on xxx = xxx #(lf)
where xxx ='”&PAM_LIST &”‘”])
in
Source
I have two dimension tables that are joined by a join key. I created two M parameters and respectively bound to a column from each dimension table. When using the columns as slicers I get error on one of them – “An incompatible filter is used on a column with a parameter”. Both parameters and columns are text. Not sure what I am doing wrong.
Do you have relationships between these tables and others used in visuals on the same page? If so, can you try deleting the relationships?
Thank you! I figured it out. I had “is not blank” filter applied on the slicers, which is not supported by M parameters. Removing the filter solved the issue.
I have another question. Your blog was helpful in passing a single M parameter to SQl where clause. Is there a way to loop through a list of M parameters to generate a concatenated Where clause. I have 8 parameters that I need to pass.
Thank you!