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

[Xairan]

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:

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:

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:

Goal:

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:

Reference table:

I split this column via delimitir into separate rows:

I also created a definition table:

The relationship between the tables:

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

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.

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

Thank you!

[v-xuxinyi-msft · Accepted solution]

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.

Output:

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.

[Xairan]

Awesome - thanks for that! Worked perfectly 🙂 

[Thennarasu_R]

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.

Thanks,
Thennarasu R

[Xairan]

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!