Last year I blogged about handling multi-select scenarios for dynamic M parameters. Since then support for “Select All” has been added (see here) but since a regular slicer with no items selected behaves the same as one with all items selected, some extra logic is needed to make a slicer bound to a dynamic M parameter behave like a regular slicer.
To illustrate this, I created a simple dataset with two tables in it:
- A DirectQuery table linked to the AdventureWorksDW DimDate table (a simple date dimension table), stored in Snowflake
- An Import mode table called Day Name that contains one column and seven rows containing the names of the days of the week
I then created an M parameter called DayParam and bound it to the DayName column of the Day Name table and enabled the Multi-select and Select all options:
Here’s the M code that shows how to use the DayParam parameter to filter the DimDate table on the EnglishDayNameOfWeek column, and handle the scenario where “Select All” has been selected as well as the scenario where no item has been selected:
let
Source = Snowflake.Databases(
"xyz.snowflakecomputing.com",
"DEMO_WH"
),
AWORKS_Database = Source
{
[
Name = "AWORKS",
Kind = "Database"
]
}
[Data],
PUBLIC_Schema = AWORKS_Database
{[Name = "PUBLIC", Kind = "Schema"]}
[Data],
DIMDATE_Table = PUBLIC_Schema
{[Name = "DIMDATE", Kind = "Table"]}
[Data],
#"Filtered Rows" =
if DayParam = null then
DIMDATE_Table
else if Type.Is(
Value.Type(DayParam),
List.Type
)
then
if DayParam = {"__SelectAll__"} then
DIMDATE_Table
else
Table.SelectRows(
DIMDATE_Table,
each List.Contains(
DayParam,
[ENGLISHDAYNAMEOFWEEK]
)
)
else
Table.SelectRows(
DIMDATE_Table,
each [ENGLISHDAYNAMEOFWEEK]
= DayParam
)
in
#"Filtered Rows"
The #”Filtered Rows” step does the following:
- If nothing is selected in the slicer then the DayParam parameter will contain a null value, and in this case no filter will be applied to the DimDate table
- If the DayParam parameter is of type list (when there is a multi-select in the slicer) then
- If it’s a list containing a single text value of “__SelectAll__”, the default value returned when the slicer has “Select all” selected, then no filter will be applied to the DimDate table
- Otherwise List.Contains will be used to filter the DimDate table by all the selected values
- Otherwise the DayParam parameter will contain a single scalar value (because a single value has been selected in the slicer) and the DimDate table will be filtered by that value
Here’s how it works:
Even after a year – still does not work for Data sources used by people from this planet – May be we just have 999 votes.
Cheers
Sam
We’re working on enabling dynamic M parameters for all DirectQuery sources, including SQL Server-related ones. More news soon I hope.
That’s great news Chris, but why restrict it to just Direct Query, why is it difficult to implement it for Import.
Dynamic M parameters by definition can’t be used for Import mode – they’re all about sending selections in slicers back to the database, and that doesn’t happen in Import mode.
Hi Chris,
Thanks for the update and detailed post. Do you have a sample query on how to pass __SelectAll__ to advance query section. Below is the query which works fine for single and multi select. But I’m looking for some pointers on how to pass all slicer values to the sql statement. Thanks!
let
selected_c_ids = if Type.Is(Value.Type(c_id_parameter), List.Type) then
Text.Combine({“‘”, Text.Combine(c_id_parameter, “‘,'”), “‘”})
else
Text.Combine({“‘”, c_id_parameter , “‘”}),
selected_all_c_ids = if Type.Is(Value.Type(c_id_parameter), List.Type) then
Text.Combine(c_id_parameter, “__selectAll__”)
else
false,
//parameters = Text.Combine({“include_all_c_ids: bool = “, Logical.ToText(selected_all_c_ids) ,”,”,
//”c_ids: dynamic = dynamic([“, selected_c_ids, “]));”}),
Source = Value.NativeQuery(
Snowflake.Databases(“X.us-east.snowflakecomputing.com”,”X_WAREHOUSE”,[Role=”X_ROLE”])
{[Name=”X_DB”]}[Data],
“select distinct v_id from X_DB.X_SCHEMA.X_TABLE
where c_id IN (“& selected_c_ids &”)”, null, [EnableFolding=true])
in
Source
Hi Chris,
Great blog, I was wondering if we can use the Power Query “Dynamic M Parameters” with SQL Store Procedure please assist with example. thanks