Filter out multiple rows based on data in just one of those rows

[Greenterer]

I have data in the following format

Student      Class
Joe             Math
Joe             English
Sally           English

Becky         Science

I want to filter out any students who have taken a math class, so that my results would look like this:

Student      Class

Sally           English
Becky         Science

My problem is that when I try to filter out anyone who took their math class that Joe's row that includes math is removed but his row that includes english class remains.  How would I get both of Joe's rows to be removed?  In other words, I only want to see people who have not taken a math class.

[danextian]

Hi @Greenterer 

Crate a disconnected table containing the distinct classes:

Create the measure below and used it as a visual filter

Filter =
VAR _count =
    CALCULATE (
        COUNTROWS ( Student ),
        FILTER (
            ALLEXCEPT ( Student, Student[Student] ),
            Student[Class] = SELECTEDVALUE ( Class[Class] )
        )
    ) + 0
RETURN
    IF ( ISBLANK ( _count ), 1, _count )

Please see the attached sample pbix.

[Ashish_Mathur]

Hi,

PBI file attached.

[ArwaAldoud · Accepted solution]

Hi @Greenterer 

You can achieve this by creating a calculated column to identify students who have taken a Math class.

apply filter to your visual will display only students who have not enrolled in a Math class.

Below is the DAX formula for the calculated column.

HasTakenMath =
VAR CurrentStudent = 'Student_Class'[Student]
RETURN
IF(
CALCULATE(
COUNTROWS('Student_Class'),
'Student_Class'[Student] = CurrentStudent,
'Student_Class'[Class] = "Math"
) > 0,
TRUE(),
FALSE()
)

PBIX file attached.

If this response was helpful, please accept it as a solution and give kudos to support other community members.