DAX NOT IN

[Anonymous]

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 

[AkhilAshok · Accepted solution]

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]

[AkhilAshok · Accepted solution]

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]

[Anonymous · Accepted solution]

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    0

I'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?

[AkhilAshok]

Why don't you apply a "Visual level filter" in the Power BI table to filter for [Passed Students] is 1

[Anonymous]

Thanks for the quick reply,

I'm surfacing the data in Excel, and not sure if I can do it in the same way?

[AkhilAshok]

How did you bring the data to Excel?

[Anonymous]

AkhilAshok

it's just what was required, simple and effective.

Many thanks

Roy.

[PattemManohar]

@Anonymous Sample data and expected output will be really helpful.