Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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")
Solved! Go to 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.
Thanks a lot. This worked as expected.
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.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |