Dax filter table for row which are not in selected value of slicer

[Rash]

We have Student Details (fact table) and Subject Details (Dim Table). Each Student has many subjects. Subject Name from Subject Details as slicer.

StudID	StudName	Sub Name
233	Jake	English
233	Jake	Computer
233	Jake	Physics
234	Nick	Computer
234	Nick	Physics
235	Hana	Physics
235	Hana	English
235	Hana	Computer

SubID	SubName
1	English
2	Computer
3	Physics

If we select "English" from subject slicer, filter rows with students who do not have "English" subject associated.

Expected Output:

234	Nick	Computer
234	Nick	Physics

Something like

select * from Student details

where StudID not in

(select StudID from Student details where SubName = "English")

[Rash]

Thanks a lot. This worked as expected.

[Rash]

@amitchandak @Ashish_Mathur can you please help on this thread!

[v-easonf-msft · Accepted solution]

Hi, @Rash 

Create a new measure:

Countrows = 
CALCULATE (
    COUNTROWS ( 'Student Details' ),
    ALLEXCEPT ( 'Student Details', 'Student Details'[StudID] ),
    'Student Details'[Sub Name] IN VALUES ( 'Subject Details'[SubName] )
) + 0
//Count the rows containing the associated SubName in each Stud
//Result 0 means that current student does not contain related records.

and then apply this measure to the visual filter pane like:

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.