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

[costing]

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

date	table_name
Monday	table_01
Tuesday	table_02
Wednesday	table_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.

[costing · Accepted solution]

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

date	table_name
Monday	table_01
Tuesday	table_02
Wednesday	table_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]

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]

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]

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.