Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
costing
Regular Visitor

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

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.

1 ACCEPTED SOLUTION
costing
Regular Visitor

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. 


View solution in original post

4 REPLIES 4
costing
Regular Visitor

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
Super User
Super User

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.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

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.

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.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.