Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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]
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?
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 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?
Why don't you apply a "Visual level filter" in the Power BI table to filter for [Passed Students] is 1
Thanks for the quick reply,
I'm surfacing the data in Excel, and not sure if I can do it in the same way?
How did you bring the data to Excel?
AkhilAshok
it's just what was required, simple and effective.
Many thanks
Roy.
@Anonymous Sample data and expected output will be really helpful.
Proud to be a PBI Community Champion
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |