Slicer that excludes instead of includes

[GigabitJack]

Hi all! First post, and newbie to Power BI. I've been working on a specific problem for weeks now and have scoured all kinds of resources, and have even tried various AI bots to get a solution, but nothing has gotten close. I think this should be very simple, but I just can't wrap my head around how to make it work in Power BI.

Here's an example of what I'm working on. The data names are different, but the relationships are exactly the same.

What I have

• Two tables: Students and GraduatingStudents
• The Students table contains student names, attendance dates, special notes, etc. with many entries for each student.
• The GraduatingStudents table contains only a single entry for each graduating student.
• A one-to-many relationship exists between GraduatingStudents and Students.
• A StudentName column exists in both tables

Students

StudentName	WeeklyAttendance	Building
Josh	Monday	A
Reema	Monday	A
Reema	Tuesday	C
Mark	Monday	D
Edmond	Friday	B
Josh	Wednesday	A
Reema	Tuesday	B

GraduatingStudents

StudentName

Reema

Mark

What I want

• I want to have a single page in my report that shows the list of Graduating Students.
• I need a way for an end-user to select one of the Graduating Students, and then they will be hidden from the other pages in the report.
• Most of my visualizations are Matrix or Bar Chart types which show counts, like number of students per day.

Assuming Reema is selected in the list of GraduatingStudents:

StudentName	WeeklyAttendance	Building
Josh	Monday	A
Edmond	Friday	B
Josh	Wednesday	A

What I've tried

• I've tried using a Slicer to show all the Graduating Students, but obviously the Slicer is trying to include students, not exclude
• I've tried multiple Slicers on the same column, but I need it to be an OR relationship not AND.
• I've tried a calculated column, but that doesn't work with the slicer.
• I've tried various Measures, but I can't figure out how to get a Measure to work on individual student names while also updating dynamically with the slicer selection.

Help I'm asking for

I'm not asking for a full solution to this, just an idea of what path to go down. I think I can figure it out eventually, but I don't even know where to go next. Happy to have any insight or ideas that anyone can offer. Thanks!

[v-kpoloju-msft]

Hi @GigabitJack,
Thank you for reaching out to the Microsoft fabric community forum.

After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used it as sample data on my end and successfully implemented it

I am also including .pbix file for your better understanding, please have a look into it:

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[v-kpoloju-msft]

Hi @GigabitJack,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[johnt75 · Accepted solution]

Create a new table with a list of graduating students, e.g.

Students for slicer = DISTINCT( 'Graduating Students'[Student Name] )

Don't create any relationships from this table, just use it in the slicer. If you want the slicer to affect all pages of the report you can use the sync slicer options to make sure that it appears on all pages. You can set it to be invisible on pages where you don't want it to appear visually, but it needs to be on every page.

Create a calculation group with a calculation item like

Exclude based on slicer =
CALCULATE (
    SELECTEDMEASURE (),
    KEEPFILTERS (
        EXCEPT (
            VALUES ( 'Graduating Students'[Student Name] ),
            VALUES ( 'Students for slicer'[Student Name] )
        )
    )
)

Add this as a report level filter, or as a page level filter on those pages where you need it.

[SamWiseOwl]

Hi @GigabitJack one way to do this is a disconnected slicer.
Create a seperate table that is not joined to the model with the items that can be ticked:

StudentName

Reema

Mark

Use this as your slicer to choose who to exclude.

Then on your table visual in the filter pane you could use a measure to apply the filter to the Filters on Visual section.

                                                            Hide Student =

                                                            IF(

                                                                SELECTEDVALUE('Student Table'[StudentName])

                                                                IN VALUES('Slicer Table'[Selected Student])

                                                                ,BLANK()

                                                                ,1)

Then apply a filter to say NOT BLANK.

Hope this gives you something to try.