having trouble with simple slicer logic

[ge97aa]

I'm a Power BI beginner. I think this should be super simple, but for whatever reason I can't get my head around it. Let's say I have the following table called MyTable:

RecordName	RecordType
A	special
B	normal
C	normal
D	special
E	normal

Now, I want a single-selection slicer with two values:

• "Show all records"
• "Show special records only"

This slicer should filter records in the manner indicated by the slicer value. i.e.:

• "Show all records" will select all records
• "Show special records only" will select ONLY records A and D (i.e. the "special" records)

That's it. I want the slicer to have ONLY those two options - either I get ALL the records or ONLY the special records. It should not allow selection of ONLY the "normal" records. How can I do this?

[Anonymous]

@ge97aa  This might not be a scalable solution but it works. Others might suggest solutions in DAX or M.

1. Create a slicer table using the Enter Data function within the Home tab of the Power Query Editor with "Show all records" rows for every RecordName and "Show Special Records Only" rows for special RecordNames
   
2. Connect the slicer table to MyTable on RecordName with the Cross filter direction set to both.
   
3. Bring the SlicerValue column from the SlicerTable in to a slicer
   

[ge97aa]

Thanks! This works for my current purposes, but I agree that it lacks scalability. I'll wait a bit for a potentially more general scalable solution before accepting yours as "the" solution.

Nonetheless, greatly appreciated.

[Anonymous · Accepted solution]

@ge97aa  A more simple approach is to create the slicer table this way. It's hard to tell if it'd be scalable without knowing more about your MyTable data source.

Then connect the tables on RecordType

It produces the same results:

[ge97aa]

Yes, I actually settled on that solution before you mentioned it 😉. It does, of course, require a many-to-many relationship, but I suspect that should not cause any unintended side effects as long as I ONLY use this new table for this specific filtering purpose.

[Ritaf1983]

Hello @ge97aa.
This can be achieved with some "cosmetic" tricks.

1. Put the record type on the slicer.
Enable the options:
"show selecet all..."
and
"multi select..."
Here is a picture

2. Sort your record types descendingly.
As shown in the picture

3. Hide "normal" by zooming out the slicer

4. Lock the form with:
"Lock aspect ratio"

5.Insert a white shape without a border to hide the vertical scroll

6. With formatting options, bring this shape to the front

7. Group the shape with the slicer

sample file here:
https://drive.google.com/file/d/1AhJuilPBnKO36-EFzD6bnFCX5BEIMSAD/view?usp=sharing 

If my answer was helpful please give me a Kudos and accept as a Solution.