Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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.
Solved! Go to 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()
)
If this response was helpful, please accept it as a solution and give kudos to support other community members.
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.
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()
)
If this response was helpful, please accept it as a solution and give kudos to support other community members.
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
61 | |
61 | |
55 | |
38 | |
27 |
User | Count |
---|---|
85 | |
60 | |
45 | |
41 | |
39 |