Reply
Xairan
Frequent Visitor
Partially syndicated - Outbound

How to get slicer AND condition when tables are not directly related

Hi all,

 

EDIT: I've solved it using https://community.fabric.microsoft.com/t5/Desktop/Slicer-with-AND-condition-in-Power-BI-instead-of-O... but the default view of the table when none of the slicers are selected are for personnel who have done all 6 courses. How do I change this so it shows all the data instead?

 

DAX Measure used:

 

Xairan_2-1725600266515.png

condition =
var selectedcourse = values(Definition[Course ID])
var countRowsReference=countrows(distinct(filter('Reference table',related(Definition[Course ID]) in selectedcourse)))
var countRowsCourse=countrows(selectedcourse)
return
if(countRowsReference>=countRowsCourse,countRowsReference,blank())

 

I applied the measure to the table so it shows items when the measure isn't blank. The measure works if I select multiple courses:

 

Xairan_4-1725600375776.png

But when none of the slicers are selected the default view shows personnel who have done all 6 courses whereas I would like the default view to show all the data.

 

Currently:

Xairan_6-1725600479665.png

Goal:

Xairan_5-1725600452749.png

 

Previous information for context:

I'm trying to solve an issue where I would like to see personnel in the company who are qualified for multiple courses however the default slicer selection uses the "OR" condition. I've found a few solutions online https://radacad.com/slicer-with-and-condition-in-power-bi however I can't seem to get this to work since the tables aren't directly related. Below is the sample data:

 

Course table:

Xairan_4-1725595981864.png

 

Reference table:

Xairan_5-1725596019534.png

 

I split this column via delimitir into separate rows:

Xairan_6-1725596045863.png

 

I also created a definition table:

Xairan_7-1725596155401.png

The relationship between the tables:

 

Xairan_8-1725596811230.png

Currently the slicer works if I'm just selecting one value:

 

Xairan_9-1725596865582.png

I would like the slicer to work so that when I select Course 1 and Course 2 I can see only names of people who have done both courses however right now it's showing people who have done either Course 1 or Course 2.

 

Xairan_10-1725596918329.png

I've also attached the Sample Data and PBIX. Help.pbix Sample Data.xlsx

 

Thank you!

1 ACCEPTED SOLUTION
v-xuxinyi-msft
Community Support
Community Support

Syndicated - Outbound

Hi @Xairan 

 

Thanks for the reply from Thennarasu_R .

 

@Xairan , the following testing is for your reference.

 

Create another measure as follows

condition1 = 
VAR _countSelected = CALCULATE(COUNT(Definition[Course]), FILTER('Definition', ISFILTERED(Definition[Course])))
return
IF(_countSelected = 0, 1, IF(_countSelected = [condition], 1, 0))

 

Put the condition1 into the visual-level filters, set up show items when the value is 1.

vxuxinyimsft_0-1725866826490.png

 

Output:

vxuxinyimsft_1-1725866858281.png

 

vxuxinyimsft_2-1725866884557.png

 

Best Regards,
Yulia Xu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-xuxinyi-msft
Community Support
Community Support

Syndicated - Outbound

Hi @Xairan 

 

Thanks for the reply from Thennarasu_R .

 

@Xairan , the following testing is for your reference.

 

Create another measure as follows

condition1 = 
VAR _countSelected = CALCULATE(COUNT(Definition[Course]), FILTER('Definition', ISFILTERED(Definition[Course])))
return
IF(_countSelected = 0, 1, IF(_countSelected = [condition], 1, 0))

 

Put the condition1 into the visual-level filters, set up show items when the value is 1.

vxuxinyimsft_0-1725866826490.png

 

Output:

vxuxinyimsft_1-1725866858281.png

 

vxuxinyimsft_2-1725866884557.png

 

Best Regards,
Yulia Xu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Syndicated - Outbound

Awesome - thanks for that! Worked perfectly 🙂 

Thennarasu_R
Responsive Resident
Responsive Resident

Syndicated - Outbound

Hi @Xairan 

Can you explain the little brief your scenario as well Can send your exception output with dummy data in excel  from below attcahed SS.

Thennarasu_R_0-1725600065281.png

Thanks,
Thennarasu R

Syndicated - Outbound

Hey, 

 

I've attached the files here Help.pbix Sample Data.xlsx

 

I've actually solved the first section myself - finding out who has done multiple courses selected however just trying to find out how to have the default view showing all the data in the table instead of everyone who has done all courses.

 

Thank you!

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

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

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)