Forum Discussion
DAX NOT IN
Hello All
I have a student exam table, where a student can have the multiple exam results.
The measure that returns the students that have not passed an exam is….
EVALUATE
ROW ( "abc", CALCULATE ( DISTINCTCOUNT ( Student_Exam[Student_ID] ),
FILTER ( Student_Exam, Student_Exam[Passed] = 0 ) ) )
The measure that returns the students that have passed an exam is….
EVALUATE ROW ( "xyz", CALCULATE ( DISTINCTCOUNT ( Student_Exam[Student_ID] ), FILTER ( Student_Exam, Student_Exam[Passed] = 1 ) ) )
What I want is the students that have passed all of their exams i.e. only appear in the second measure above ( a student might have 2 exams, one pass and one fail and would appear in both measure results)
In SQL terms, I’m looking for …
SELECT Student_ID FROM dbo.Student_Exam
WHERE Student_ID NOT IN (
SELECT Student_ID FROM dbo.Student_Exam
WHERE Passed=0
GROUP BY Student_ID
)
GROUP BY Student_ID
I’ve tried numerous variations of IN, CONTAINS etc, I searched on line for a solution, but cannot get it to work. I'm nearing the pulling the hair out of my head phase.
Any and all suggestions welcome
Roy
How about calculating students who failed in alteast one Exam, and subtractign that from Total Students?
Total Students = DISTINCTCOUNT(Student_Exam[Student_ID]) Failed Students = CALCULATE([Total Students], Student_Exam[Passed] = 0) Passed Students = [Total Students] - [Failed Students]
Many thanks for the assistance with this issue. It' working almost exactly as I want it. The one problem, is that when I pull the value into excel (the total numbers are correct) I get back all Students as in the table below. All I need is the 1 values.
Student_Name # Passed Students
Nick Smith 1
Frank Lloyd 0
Mary Jones 1
Alison Connelly 0
Brendan Doyle 0
Derek Kelly 1
John Foster 0I've changed the the filter context in the Failed Student measure to
CALCULATE (
[Total Student],
FILTER(Student_Exam, Student_Exam[Passed] = 0 )),I tried filtering etc, but can't get it to work. Maybe I'm mixing granularity, but thought this would be pretty straight forward just to return a list of Stutents that have only passed?
7 Replies
How about calculating students who failed in alteast one Exam, and subtractign that from Total Students?
Total Students = DISTINCTCOUNT(Student_Exam[Student_ID]) Failed Students = CALCULATE([Total Students], Student_Exam[Passed] = 0) Passed Students = [Total Students] - [Failed Students]
Many thanks for the assistance with this issue. It' working almost exactly as I want it. The one problem, is that when I pull the value into excel (the total numbers are correct) I get back all Students as in the table below. All I need is the 1 values.
Student_Name # Passed Students
Nick Smith 1
Frank Lloyd 0
Mary Jones 1
Alison Connelly 0
Brendan Doyle 0
Derek Kelly 1
John Foster 0I've changed the the filter context in the Failed Student measure to
CALCULATE (
[Total Student],
FILTER(Student_Exam, Student_Exam[Passed] = 0 )),I tried filtering etc, but can't get it to work. Maybe I'm mixing granularity, but thought this would be pretty straight forward just to return a list of Stutents that have only passed?
Why don't you apply a "Visual level filter" in the Power BI table to filter for [Passed Students] is 1
Anonymous Sample data and expected output will be really helpful.
