Create a calculated table which acts as a slicer to filter a table

[JemmaD]

At the moment I have five tables, all pre-filtered to different things and I have five bookmarks showing/hiding each table depending on which view the user wants to see. 

Call the views 'VIEW 1' 'VIEW 2' 'VIEW 3'...

Instead of bookmarks and buttons I had an idea to create a calculated table which has a column called 'View' which lists all five views, and the other attribute of the table would be to declare the filters.

Then I would place 'View' column in a slicer, and a single unfiltered table on the page, and as people change the slicer, it filters the table.

I have no idea if these functionality exists and i've just made it up entirely. If you have any ideas, please let me know!

[Anonymous · Accepted solution]

Hi @JemmaD ,

I have two table:

You can follow the below steps:

1.Create a slicer visual with table(2) and create a table visual with Table(3)

2.Create a measure, then choose table visual and add it to filter conditions in filter pane for table visual

Measure2 =
VAR slicerval =
    MAX ( 'Table (2)'[View] )
VAR view1 =
    CALCULATE (
        SUM ( 'Table (3)'[Amount] ),
        FILTER (
            'Table (3)',
            'Table (3)'[Category] = "a"
                || 'Table (3)'[Category] = "b"
        )
    )
VAR view2 =
    CALCULATE (
        SUM ( 'Table (3)'[Amount] ),
        FILTER (
            'Table (3)',
            'Table (3)'[Category] = "c"
                || 'Table (3)'[Category] = "d"
        )
    )
RETURN
    IF ( slicerval = "View1", view1, IF ( slicerval = "View2", view2, BLANK () ) )

3.When you change the slicer value, can filter the table value

If I misunderstand your demands, please feel free to let me know.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PaulDBrown]

I'm not sure how you are setting up the bookmarks/buttons, but with the inclusion of the buttons based on bookmarks, it's simple to set up, and the behaviour emulates that of a slicer

[JemmaD]

@PaulDBrown Yes that's how i've set up the bookmarks, but I would rather avoid them altogether if possible, they're clunky, slow and very manual. 

[amitchandak]

@JemmaD , I assume already explored field parameters can not help

https://amitchandak.medium.com/power-bi-field-parameters-a-quick-way-for-dynamic-visuals-fc4095ae9af...

Looking at description field parameter and bookmark, both might be needed

[JemmaD]

@amitchandak your link suggestion was the inspiration for my idea, but it just changes the axis and I need it not to change the axis, but to filter the data.

I was hoping I could say View Selection = {
"View1", FILTER(
'table1'[category]="a" ||

'table1'[category]="b",

"View 2", FILTER(

'table1'[category]="c" ||

'table1'[category]="d")}

I guess i'm stuck with wretched bookmarks for now 😞