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
Rash
Frequent Visitor

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

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

StudIDStudNameSub Name
233JakeEnglish
233JakeComputer
233JakePhysics
234NickComputer
234NickPhysics
235HanaPhysics
235HanaEnglish
235Hana

Computer

 

SubIDSubName
1English
2Computer
3Physics

 

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

 

Expected Output:

234NickComputer
234NickPhysics

 

Something like

select * from Student details

where StudID not in

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

1 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:

veasonfmsft_0-1655363780467.png

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.

View solution in original post

3 REPLIES 3
Rash
Frequent Visitor

Thanks a lot. This worked as expected.

Rash
Frequent Visitor

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

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:

veasonfmsft_0-1655363780467.png

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.

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.