Assign students a pass/fail status based on how many tests they've passed

[afaherty]

Hi All,

I was hoping one of you talented forum members could help me.

Here is an example of my data:

Essentially, what I need to do is designate each student as Pass or Fail overall based on how many tests they've passed/failed.  In order to pass overall, they need to have passed 1/2 or more of the tests (so 3 or more tests A-F).  Then, I need to count how many were designated as passed overall.  Can anyone help?  Thank you so much!

[smpa01 · Accepted solution]

@afaherty  PFA

[tamerj1 · Accepted solution]

HI @afaherty 
I hope something like this would satisfy your requirements.

Pass/Fail = 
VAR PassLimit = SELECTEDVALUE ( 'Pass Limit'[Pass Limit] )
VAR CurrentStudentTable =
    CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Student], 'Table'[Test] ) )
VAR Passes =
    FILTER ( CurrentStudentTable, 'Table'[Pass_Fail] = "Pass" )
VAR Result =
    IF (
        DIVIDE ( COUNTROWS ( Passes ), COUNTROWS ( CurrentStudentTable ) ) >= PassLimit,
        "Pass",
        "Fail"
    )
VAR CurrentResult = SELECTEDVALUE ( 'Table'[Pass_Fail] )
RETURN
    IF (
        HASONEVALUE ( 'Table'[Subtest] ),
        CurrentResult,
        Result
    )

Result = 
IF ( 
    ISINSCOPE ( 'Table'[Test] )
        && ISINSCOPE ( 'Table'[Student] ),
    [Pass/Fail],
    SUMX ( 
        SUMMARIZE ( 'Table', 'Table'[Student], 'Table'[Test] ), 
        IF ( [Pass/Fail] = "Pass", 1 ) 
    )
)

[tamerj1 · Accepted solution]

@afaherty 
Please refer to attached

Pass/Fail = 
VAR PassLimit = SELECTEDVALUE ( 'Pass Limit'[Pass Limit] )
VAR CurrentStudentTable =
    CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Student], 'Table'[Test] ) )
VAR Passes =
    FILTER ( CurrentStudentTable, 'Table'[Pass_Fail] = "Pass" )
VAR Result =
    IF (
        DIVIDE ( COUNTROWS ( Passes ), COUNTROWS ( CurrentStudentTable ) ) >= PassLimit,
        "Pass",
        "Fail"
    )
VAR CurrentResult = SELECTEDVALUE ( 'Table'[Pass_Fail] )
RETURN
    IF (
        HASONEVALUE ( 'Table'[Subtest] ),
        CurrentResult,
        Result
    )

Result = 
VAR T = SUMMARIZE ( 'Table', 'Table'[Student], 'Table'[Test] )
VAR Students = COUNTROWS ( T )
VAR Passed = SUMX ( T, IF ( [Pass/Fail] = "Pass", 1, 0 ) )
VAR PassedPCT = FORMAT ( DIVIDE ( Passed, Students ), "Percent" )
RETURN
IF ( 
    ISINSCOPE ( 'Table'[Test] ),
    IF ( 
        ISINSCOPE ( 'Table'[Student] ),
        [Pass/Fail],
        "Passed " & Passed & " Student(s)" & UNICHAR ( 10 ) & "(" & PassedPCT & ")"
    )
)

[tamerj1 · Accepted solution]

HI @afaherty 
I hope something like this would satisfy your requirements.

Pass/Fail = 
VAR PassLimit = SELECTEDVALUE ( 'Pass Limit'[Pass Limit] )
VAR CurrentStudentTable =
    CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Student], 'Table'[Test] ) )
VAR Passes =
    FILTER ( CurrentStudentTable, 'Table'[Pass_Fail] = "Pass" )
VAR Result =
    IF (
        DIVIDE ( COUNTROWS ( Passes ), COUNTROWS ( CurrentStudentTable ) ) >= PassLimit,
        "Pass",
        "Fail"
    )
VAR CurrentResult = SELECTEDVALUE ( 'Table'[Pass_Fail] )
RETURN
    IF (
        HASONEVALUE ( 'Table'[Subtest] ),
        CurrentResult,
        Result
    )

Result = 
IF ( 
    ISINSCOPE ( 'Table'[Test] )
        && ISINSCOPE ( 'Table'[Student] ),
    [Pass/Fail],
    SUMX ( 
        SUMMARIZE ( 'Table', 'Table'[Student], 'Table'[Test] ), 
        IF ( [Pass/Fail] = "Pass", 1 ) 
    )
)

[afaherty]

@tamerj1 THANK YOU! This works beautifully as well!  I know I've already asked user smpa, but after completing your steps above, how could I then get the percentage of students who were designated as "passed" for each test (we don't care about subtest)?

[tamerj1 · Accepted solution]

@afaherty 
Please refer to attached

Pass/Fail = 
VAR PassLimit = SELECTEDVALUE ( 'Pass Limit'[Pass Limit] )
VAR CurrentStudentTable =
    CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Student], 'Table'[Test] ) )
VAR Passes =
    FILTER ( CurrentStudentTable, 'Table'[Pass_Fail] = "Pass" )
VAR Result =
    IF (
        DIVIDE ( COUNTROWS ( Passes ), COUNTROWS ( CurrentStudentTable ) ) >= PassLimit,
        "Pass",
        "Fail"
    )
VAR CurrentResult = SELECTEDVALUE ( 'Table'[Pass_Fail] )
RETURN
    IF (
        HASONEVALUE ( 'Table'[Subtest] ),
        CurrentResult,
        Result
    )

Result = 
VAR T = SUMMARIZE ( 'Table', 'Table'[Student], 'Table'[Test] )
VAR Students = COUNTROWS ( T )
VAR Passed = SUMX ( T, IF ( [Pass/Fail] = "Pass", 1, 0 ) )
VAR PassedPCT = FORMAT ( DIVIDE ( Passed, Students ), "Percent" )
RETURN
IF ( 
    ISINSCOPE ( 'Table'[Test] ),
    IF ( 
        ISINSCOPE ( 'Table'[Student] ),
        [Pass/Fail],
        "Passed " & Passed & " Student(s)" & UNICHAR ( 10 ) & "(" & PassedPCT & ")"
    )
)

[afaherty]

@tamerj1 I'm running into a problem that I can't figure out.

This student has Test 4, but it's not showing up in the matrix.  Here's what their raw data looks like:

Student	Test	Pass_Fail	Subtest
1	4	Fail	A
1	4	Pass	A

I realize the subtest for both rows is the same, but that's the nature of my data - the students can take a subtest more than once and we still want to count all of them.

In the matrix, I do see her test 2, test 6, and test 7 but there's no test 4.  Here's what the matrix looks like:

Student	Test 2	Test 6	Test 7
1	Fail	Fail	Pass

Unfortunately they are not the only student with this problem.  I'm finding more missing from the matrix.

Thoughts?

[tamerj1 · Accepted solution]

@afaherty 

[afaherty]

Thank you, you thank you!  You are immensely talented!

[afaherty]

Excellent! Thank you!

[afaherty]

@tamerj1 Thank you for your help.  What if my criteria changed from passing half....to passing 80%? Just change it to 0.8 right?  I tried that but it's assigning a "fail" status to students who should be "pass"

[v-yueyunzh-msft]

Hi , @afaherty 

According to your description, you want to "Assign students a pass/fail status based on how many tests they've passed".

Here are the steops you can refer to :
(1)My test data is the sam eas yours.

(2)We need to create a measuyre like this:

Measure = var _need_pass_count= COUNTROWS(VALUES('Table'[Test]))/2
var _t =SUMMARIZE('Table','Table'[Student],"Pass" , CALCULATE(COUNT('Table'[Pass_Fail]) ,'Table'[Pass_Fail]="Pass"))
return
COUNTROWS(FILTER(_t ,[Pass]>=_need_pass_count))

(3)Then we can get the value "2":

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[afaherty]

Thank you for your help.  What if my criteria changed from passing half....to passing 80%?

[smpa01]

@afaherty  you can use a measure like this

Measure 2 = 
VAR passingCriteria = 0.8
VAR grpBy =
    ALLEXCEPT ( 'Table', 'Table'[Student] )
VAR total =
    CALCULATE ( COUNT ( 'Table'[Test] ), grpBy )
VAR passCount =
    CALCULATE ( COUNT ( 'Table'[Test] ), grpBy, 'Table'[Pass_Fail] = "Pass" )
VAR ratio =
    DIVIDE ( passCount, total )
VAR ternary =
    SWITCH ( TRUE (), ratio < passingCriteria, "fail", "true" )
RETURN
    ternary

[afaherty]

@smpa01 Thank you so much!  Unfortunately it doesn't seem to be working.  I created a matrix of student ID's by Test to check it, and it's showing that students failed when they actually should be classified as passed.  For example, when they had 5 ouf of 6 tests passed.

[smpa01]

@afaherty  works for me, PFA

[afaherty]

@smpa01 You're always so helpful.  I can't attach files, but here's an example of what I'm encountering. So for this student, he should be "pass" for test 5 and test 6 but it's showing fail.  (Subtest doesn't matter, it's just how my actual real data is set up).

Student	Subtest	Test	PassFail
1	A	1	Pass
1	B	1	Pass
1	C	1	Fail
1	D	1	Pass
1	A	2	Pass
1	B	2	Pass
1	C	2	Fail
1	D	2	Pass
1	E	2	Pass
1	A	4	Pass
1	B	4	Fail
1	C	4	Pass
1	A	5	Pass
1	A	6	Fail
1	B	6	Fail
1	C	6	Pass
1	D	6	Pass
1	E	6	Pass
1	F	6	Fail
1	G	6	Pass
1	H	6	Pass
1	I	6	Pass
1	J	6	Fail

Results:

Measure I used, so kindly provided by you:

[smpa01]

The sample data does not look correct, It is the same Student (1) across all rows

[afaherty]

@smpa01 Yeah, I just narrowed in on student #1 only, just to show how the measure isn't working out for me.  Sorry for the confusion.  I really wish I could attach files here!  

[smpa01]

I am not understaning the Req.

he should be "pass" for test 5 and test 6 - based on what?

[afaherty]

Ah, you are correct about Test 6 - he only passed 6 of the 10 subtests which is not 80%.  My fault!  He should be "Pass" for test 5 though because there is only one subtest, and he passed it so he passed 100% of them.

[smpa01 · Accepted solution]

@afaherty  PFA

[afaherty]

@smpa01 THANK YOU!! It works beautifully now!  One last question.  After completing your 4   measures, how can I get the percentage of students who were designated as "pass" for each test (don't care about subtest)?  I thought maybe your Measure3 would show that but it doesn't seem to be the case.