Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
afaherty
Helper IV
Helper IV

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

Hi All,

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

 

Here is an example of my data:

 

afaherty_1-1675706743103.png

 

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!

 

4 ACCEPTED SOLUTIONS

@afaherty  PFA

 

smpa01_0-1675806870884.png

 

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

tamerj1
Super User
Super User

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

1.png2.png

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 ) 
    )
)

View solution in original post

@afaherty 
Please refer to attached

1.png

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 & ")"
    )
)

View solution in original post

21 REPLIES 21
tamerj1
Super User
Super User

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

1.png2.png

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 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)?

@afaherty 
Please refer to attached

1.png

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 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:

 

StudentTestPass_FailSubtest
14FailA
14PassA

 

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:

 

StudentTest 2Test 6Test 7
1FailFailPass

 

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

Thoughts?

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

Excellent! Thank you!

afaherty
Helper IV
Helper IV

@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
Community Support
Community Support

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":

vyueyunzhmsft_0-1675735270118.png

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

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

@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
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

@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.

@afaherty  works for me, PFA

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

@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).

 

StudentSubtestTestPassFail
1A1Pass
1B1Pass
1C1Fail
1D1Pass
1A2Pass
1B2Pass
1C2Fail
1D2Pass
1E2Pass
1A4Pass
1B4Fail
1C4Pass
1A5Pass
1A6Fail
1B6Fail
1C6Pass
1D6Pass
1E6Pass
1F6Fail
1G6Pass
1H6Pass
1I6Pass
1J6Fail

 

Results:

afaherty_0-1675802700761.png

 

Measure I used, so kindly provided by you:

 

afaherty_1-1675802725502.png

 

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

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

@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!  

I am not understaning the Req.

 

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

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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.

@afaherty  PFA

 

smpa01_0-1675806870884.png

 

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

@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.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors