Handling “Select All” For Slicers Bound To Dynamic M Parameters In Power BI

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:

  1. A DirectQuery table linked to the AdventureWorksDW DimDate table (a simple date dimension table), stored in Snowflake
  2. 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:

5 responses

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

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

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

Leave a Reply

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

%d bloggers like this: