Slicer to include or exclude rows with specific text

[Anonymous]

Hello,

I have the following table and i am trying to make a slicer that would show a checkbox with the two options: "Yes" or "No". The selection in the checkbox would either show all rows including, otherwise excluding the text "not received _ no group", respectively. Is that possible? Any help is much appreciated!

Document ID	Amount	Received date	Text
24897	3989	06/07/2022	not received _ no group
27387	1781	07/07/2022	not received _ no group
25119	4510	06/07/2022	cleared
25406	6467	05/07/2022	not received _ no group
24924	6570	05/07/2022	not received _ no group
24893	4437	06/07/2022	received _ pending
25403	6554	05/07/2022	received _ pending
24845	6408	05/07/2022	not received _ no group
24924	7049	06/07/2022	cleared
24618	6411	05/07/2022	cleared
24622	4413	05/07/2022	cleared
24891	4372	05/07/2022	not received _ no group
24891	6729	05/07/2022	cleared
24925	6770	05/07/2022	recorded in advance
27415	6548	05/07/2022	recorded in advance
27415	6414	05/07/2022	recorded in advance
27415	6450	05/07/2022	not received _ no group
27415	6544	05/07/2022	cleared
27415	6546	05/07/2022	invoiced
27415	6583	05/07/2022	invoiced
27415	6615	05/07/2022	recorded in advance

[TheoC · Accepted solution]

Hi @Anonymous 

You can create a calculated column that you can then use for the slicer.  Just create a conditional formula to use in the calculated column like the following:

Slicer = 

SWITCH (
     TRUE () ,
          'Table'[Text] = "not received _ no group" , "Yes" ,
          "No" 
       )

You can then use the new calculated column to create the Slicer you want 🙂

Hope this helps!

Theo

[Anonymous]

@TheoC Thanks for your reply! Is it possible to add a third option to the slicer: "Show all" where the slicer would have no effect, that is, it will show all rows and not filter anything?

[TheoC]

Hi @Anonymous you can do this by clicking on the actual Slicer visual and using the options to "select all".  Hopefully this is what you're after.

[Anonymous]

@TheoC Thanks for this suggestion but i would like to keep it as a simple single-click checkbox selection list. The issue with the "Select all" option is that if it is selected, then if another option is selected, then it actually excludes the latter instead of moving to that selection only.

For example, after clicking on the "Select all" option:

Then, if the option "No" is clicked, instead of selecting only "No", it actually excludes it:

So, i think maybe having an option in the formula where it would do the same as "Select all" or select none. Is that possible?

[TheoC]

Hi @Anonymous you can just tick "Single Select" from the options. There is no need for additional formulas etc.

Hope that helps.