Forum Discussion

costing's avatar
costing
Frequent Visitor
2 years ago
Solved

Slice using different column from the one bound to an M query parameter

I have a slicer (single select) on a date column in this slicer table:

 
datetable_name
Mondaytable_01
Tuesdaytable_02
Wednesdaytable_03
 

The table_name column is bound to an M query parameter that is used to determine which table gets loaded.

 

How can I get the table_name parameter to change when the slicer is updated?

 

(If table_name is used in the slicer, this happens automatically, but the user only understands selecting by date, not by table_name)

Please note: this is just an example, the actual slicer table is dynamically generated and much larger.

Need Help
  • costing's avatar
    costing
    2 years ago

    Hey danextian , thank you for the conversation. 

    I've found a way to do it.  The steps are:
    - bind date column to a parameter date_parameter (instead of table_name.)

    - in power query, look up table_name in the let statement before loading the table.

     

    Here's the code, where SLICER_TABLE refers to

    datetable_name
    Mondaytable_01
    Tuesdaytable_02
    Wednesdaytable_03

     

     

    let
    my_date = Text.From(date_parameter),
    my_table = List.First(
        Table.Column(
            Table.SelectRows(SLICER_TABLE, each [DATE] = my_date)
            , "TABLE_NAME"
        )
    ),
    .... (then use my_table as the table to load)

     

     
    I'm new to mashup, so if there's a more elegant way, please let me know. 


4 Replies

  • costing's avatar
    costing
    Frequent Visitor
    2 years ago

    Hey danextian , thank you for the conversation. 

    I've found a way to do it.  The steps are:
    - bind date column to a parameter date_parameter (instead of table_name.)

    - in power query, look up table_name in the let statement before loading the table.

     

    Here's the code, where SLICER_TABLE refers to

    datetable_name
    Mondaytable_01
    Tuesdaytable_02
    Wednesdaytable_03

     

     

    let
    my_date = Text.From(date_parameter),
    my_table = List.First(
        Table.Column(
            Table.SelectRows(SLICER_TABLE, each [DATE] = my_date)
            , "TABLE_NAME"
        )
    ),
    .... (then use my_table as the table to load)

     

     
    I'm new to mashup, so if there's a more elegant way, please let me know. 


  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    2 years ago

    Hi costing ,

    M query parameters affect the data model but it doesn't work the other way around. A slicer selection cannot be passed on as a parameter. You need to load the table with all the needed rows and use a slicer to change the visible rows in a visual.

    • costing's avatar
      costing
      Frequent Visitor
      2 years ago

      If I use table_name in the slicer it works fine, but users don't know which table_name to choose.  So I need to use date column for the slicer.  

       

      Hope that is clearer.
       

      A related use case: users choose from a drop down control according to the display value (like a product name, e.g bananas) and in the code it would pass the actual value (product id e.g. 12345), and it would be the product id that is used to filter a table.

      • danextian's avatar
        danextian
        Icon for Super User rankSuper User
        2 years ago
        date table_name
        Monday table_01
        Tuesday table_02
        Wednesday table_03

        For this, the query must be designed in such a way that all this info is loaded, meaning you must load a table that contains the Day, the table name and all other relevant columns and tables. The columns/fields are then added to a table visual and are filtered  using a slicer.

        The same goes for the related use case. All the relevant information fields must be loaded into the model so they can be filtered using the slicer selections.

        Note: if you're trying to create a calculated table that changes with the slicer selection, that is not possible. Calculated tables and columns are updated only when the referenced tables/columns are updated or whent he underlying data has been refreshed.  Slicers are meant to filter visuals and not the tables in the model.