Forum Discussion

silkwire's avatar
silkwire
Frequent Visitor
3 years ago
Solved

Filter table by different column than slicer selection value

I have a scenario where I want to list a table of stores filtered by one level higher hierarchy than the store selected.

 

So normal, slicer interaction, the user selects the store from slicer and the table is filtered to that store:

 

but I want the table to display all stores in the selected store's area, not just the single store. Like this:

 

How do I accomplish this? 

Need Help
  • johnt75's avatar
    johnt75
    3 years ago

    You could create a disconnected copy of the stores and areas for use in the slicer, e.g.

    Table for slicer = SUMMARIZE( 'Table', 'Table'[Store], 'Table'[Area])

    Use the store column from this new table on the slicer, then create a measure like

    Store is visible =
IF (
    SELECTEDVALUE ( 'Table'[Store] )
        IN CALCULATETABLE (
            VALUES ( 'Table'[Store] ),
            TREATAS ( VALUES ( 'Table for slicer'[Area] ), 'Table'[Area] )
        ),
    1
)

    and add that as a visual level filter to your table, set to show only when the value is 1.

2 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    You could create a disconnected copy of the stores and areas for use in the slicer, e.g.

    Table for slicer = SUMMARIZE( 'Table', 'Table'[Store], 'Table'[Area])

    Use the store column from this new table on the slicer, then create a measure like

    Store is visible =
IF (
    SELECTEDVALUE ( 'Table'[Store] )
        IN CALCULATETABLE (
            VALUES ( 'Table'[Store] ),
            TREATAS ( VALUES ( 'Table for slicer'[Area] ), 'Table'[Area] )
        ),
    1
)

    and add that as a visual level filter to your table, set to show only when the value is 1.