Create a table to generate a pass rate

[Gusd8]

Thank you for looking, I am not sure why I am struggling with this...

Let's say we have a 20 Classes. In those 20 classes there is a total class roster of 500 students spread between them. A student is only going to be in one class. Each class has a varying number of tests, but to pass the class you have to pass all of the tests for the class.

Next is a 2,000 record table that has Student ID, Test ID and Pass-Fail. (we can get class ID via a join to a class/student table). The goal is to have a Visual that shows Class/Student Count/PassRate where the pass rate is based on students that have passed all assigned tests. Small example below...

Student	Test	Pass	Fail
S1	T1	1	0
S1	T2	1	0
S1	T3	1	0
S2	T1	1	0
S2	T2	0	1
S2	T3	1	0
S3	T1	0	1
S3	T2	0	1
S3	T3	1	0

Assuming those 3 students are in the same class...what I want to ultimately be able to see from that data is that only 1 of 3 students has passed all of their tests

Class	Students	Pass Rate
C1	3	.33.33%

I don't know wht I am struggling so much with this...any assistance with solving this would be greatly appreciated. Thank you for your insight.

[Ashish_Mathur]

Hi,

Share the download link of your PBI file.

[Gusdate2]

PBIX FILE LINK  

I generated a pbix file with a dataset that matches what I am looking to do.

I hope it makes sense. Thank You for looking...

[danextian · Accepted solution]

HI @Gusd8 

Given your current data, first I would compute for the count of unique students and then the student with failed subjects only (assuming that if a student has only 0s in fail column, he passes all the subjects). I'd use those calculations to compute for the pass rate. Try this formula below as a measure.

Pass Rate2 = 
VAR StudenstWithFails =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Student] ),
        FILTER ( 'Table', 'Table'[Fail] > 0 )
    )
VAR TotalStudents =
    DISTINCTCOUNT ( 'Table'[Student] )
RETURN
DIVIDE ( TotalStudents - StudenstWithFails, TotalStudents )

[Gusdate2]

YOU ARE AWESOME!!!

I created a sample data set and basically copy and pasted your dax and it appears to work perfectly. I am so impressed. I hadn't thought of using VAR, and I am still a bit fuzzy on exactly what is happening...but I will be working to break it down today. Thank You...again I am so impressed.

There is one other need I am going to have and that is to be able to toggle between all students and students that have passed all tests and students that still have tests to pass. If you are interested in looking...I pushed a sample file out. I will start playing with that this afternoon too.