Need help - control default data to display and slicer select

[Babette]

Hi,

I am not sure how i can explain my problem so I will try to illustrate using pictures to help describe.

The original request was to show in the PBI report Staff who are not Contractor and not Admin.
So my SQL (using Import, PBI on prem) for Staff table filters out ..where IsContractor <> 'Yes' and IsAdmin <> 'Yes' to build the 'Group' slicer.
Users can multi-select in the 'Group' slicer what they want to show in the report.
All other data tables that I imported have Staff name which i use to join to the Staff 'Group' table.

The client wants a change:
1) Add a 'Include' slicer - 'Contractor', 'Administrator'
2) The report by default displays staff IsContractor <> 'Yes' and IsAdmin <> 'Yes'
3) Users can then select in the 'Include' slicer to display the contractors/administrators in addition to the default display.

I don't know how i can achieve this. Hope the PBI community can help suggest what i can do. Thank you in advance.

This is the Staff table I extract to make the 'Group' slicer.

[AllisonKennedy]

@Babette The simple way to do this would be to configure your table so that you have Three columns:

Staff

Group

Type

where Type is Contractor, Admin or Other.

You can do this with your current table using Unpivot.

Once you have that, load all the data into the report and add a slicer for [Type]. Select 'Other' in the slicer, but viewers can choose to add in Admin or Contractors if they desire. You may wish to edit the slicer formatting 'selection controls' so that it's more user friendly.

If you MUST have an include slicer only, you could use some advanced DAX and parameter tables.

[Babette]

@AllisonKennedy   The client wants the report default to display all people but (Contractor, Admin).  They want a 'Include' slicer with Contractor, Admin options so they can pick what to include in the display.
I am beginner DAX.  Could you provide a sample of DAX and parameter tables i need to solve this please?  Thank you.

[AllisonKennedy · Accepted solution]

@Babette  I created a sample file, below signature.

I added a conditional column in Power Query to get Position Type. 

OPTION A:

Use this column as a slicer. Set it to show 'Staff' only. This will be the default. If users want to add contractor or admin they can.

UPDATE - added a new version of the file that turns multi-select with ctrl to OFF for this slicer too. https://excelwithallison.blogspot.com/2022/02/power-bi-formatting.html 

OPTION B:

Create a new table that's not related to your data model (distinct list of position types you want to see in the slicer). Use this in the slicer.

Create a DAX measure to filter the results and add that DAX measure as a filter on this visual to ALL visuals you want to update based on the slicer selection. 

Option A is much more efficient if you can convince them it's ok, but Option B is what you/they are asking for exactly.

Hope it helps. Let me know if you get stuck replicating any part of it.