Create slicer Yes / No to filter the grid.

[Ethanhunt123]

Hello, I have a slicer that shows various colour (Red, Blue, Green). I have a grid that shows details for fruits (name, color, size, taste etc). I want to create a slicer with Yes/ No option for the user, If the user selects "yes" then filter the grid with colour slicer if the user selects "no" then do not filter the grid with colour slicer (show all colour in the grid, irrespective of the selection in the colour slicer). Just like the sync capability but with dynamic Yes/no.

[v-deddai1-msft]

Hi @Ethanhunt123 ,

It seems that you need button to clear slicer selections in your report. As mentioned by MFelix, you can realize it by using button and bookmark. I suggest you follow the detailed steps in https://radacad.com/clear-all-slicers-in-power-bi-a-bookmark-story

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[MFelix]

Hi @Ethanhunt123 ,

You need to create a disconnected table with Yes/No and another one with the colours then add the following measures:

Filter Table =
SWITCH (
    TRUE ();
    SELECTEDVALUE ( Apply_Filter[Yes/No] ) = "No"; 1;
    SELECTEDVALUE ( Apply_Filter[Yes/No] ) = "YES"
        && SELECTEDVALUE ( 'Table'[Colour] ) IN VALUES ( Colour_Slicer[Colour] ); 1;
    BLANK ()
)

Now filter the visualization based on values equal to 1.

Check PBIX file attach.

[Ethanhunt123]

@MFelix  That slicer for color is on the main (setup page) that is affecting many other tabs in the report. I cannot use disconnected table slicer. It will not filter data for anything

[MFelix]

Hi @Ethanhunt123 ,

Did not realized you add that limitation. Since it's based on that slicer maybe the best option is to create two similar tables one with the interactions with the slicer and another without then create two bookmarks and associate them to a Yes / No buttons and that will present one table or another one.

[Ethanhunt123]

@MFelix  I cannot create a bookmark that tab already has a bookmark. 

[MFelix]

Hi @Ethanhunt123

You can create multiple bookmarks there is no issue on having multiple bookmarks on the same page.

I have a report with only a single page but I give 10 different reports pages just using bookmarks. 

[amitchandak]

@Ethanhunt123 , isfiltered or has one value should help

example

if(isfiltered(YesNo[YesNo]), [Measure], calculate([Measure], all(Table[color]))

if(isfiltered(YesNo[YesNo]), [Measure], calculate([Measure], removefilters(Table[color]))

https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/

https://docs.microsoft.com/en-us/dax/removefilters-function-dax