Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nevjohnson
Regular Visitor

Conditional Filter

Dear Community,

 

I cannot seem to find a simple way to create a filter on two items with an AND condition,.

Take the example dataset below.

How can I easily represent/filter on students that have a qualification in Mathematics and Physics? Thanks in advance for your guidance. Br, Nev 

 


Student Qualification In
A History
A Mathematics
B Spanish
B Physics
B Mathematics
C Mathematics
C Physics
D Physics
E Geography
E Physics
E Mathematics
E History

1 ACCEPTED SOLUTION
v-huizhn-msft
Employee
Employee

Hi @nevjohnson,

After research and test, I can get the expected result. When you select both Mathematics and Physics, I create a measure to decide whether the student have choosed both of the class, it will return 1 if he have both of the classes, otherwise, it returns 0. Then put the measure in visual level filter.

1.PNG

Both the qualification or not =
IF (
CALCULATE ( DISTINCTCOUNT ( Table1[Qualification] ), ALLSELECTED ( Table1 ) )
= CALCULATE (
DISTINCTCOUNT ( Table1[Qualification] ),
ALLSELECTED ( Table1[Qualification] )
),
1,
0
)


Please download the .pbix file for more detailed information.

Best Regards,

Angelia

View solution in original post

1 REPLY 1
v-huizhn-msft
Employee
Employee

Hi @nevjohnson,

After research and test, I can get the expected result. When you select both Mathematics and Physics, I create a measure to decide whether the student have choosed both of the class, it will return 1 if he have both of the classes, otherwise, it returns 0. Then put the measure in visual level filter.

1.PNG

Both the qualification or not =
IF (
CALCULATE ( DISTINCTCOUNT ( Table1[Qualification] ), ALLSELECTED ( Table1 ) )
= CALCULATE (
DISTINCTCOUNT ( Table1[Qualification] ),
ALLSELECTED ( Table1[Qualification] )
),
1,
0
)


Please download the .pbix file for more detailed information.

Best Regards,

Angelia

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.