topic Re: Assign students a pass/fail status based on how many tests they've passed in DAX Commands and Tips

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

afaherty — Mon, 06 Feb 2023 18:08:16 GMT

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!

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

tamerj1 — Mon, 06 Feb 2023 18:41:29 GMT

Hi afaherty

please try

Pass/Fail =
VAR CurrentStudentTable =
CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Student] ) )
VAR Passes =
FILTER ( CurrentStudentTable, 'Table'[Pass_Fail] = "Pass" )
RETURN
IF (
DIVIDE ( COUNTROWS ( Passes ), COUNTROWS ( CurrentStudentTable ) ) >= 0.5,
"Pass",
"Fail"
)

Passes =
SUMX ( VALUES ( 'Table'[Student] ), IF ( [Pass/Fail] = "Pass", 1 ) )

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

v-yueyunzh-msft — Tue, 07 Feb 2023 02:01:28 GMT

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

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

afaherty — Tue, 07 Feb 2023 19:03:48 GMT

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

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

afaherty — Tue, 07 Feb 2023 19:37:27 GMT

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"

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

smpa01 — Tue, 07 Feb 2023 19:17:37 GMT

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

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

afaherty — Tue, 07 Feb 2023 19:33:06 GMT

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.

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

smpa01 — Tue, 07 Feb 2023 19:39:53 GMT

afaherty works for me, PFA

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

afaherty — Tue, 07 Feb 2023 20:47:14 GMT

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:

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

smpa01 — Tue, 07 Feb 2023 21:33:00 GMT

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

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

afaherty — Tue, 07 Feb 2023 21:37:48 GMT

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!

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

smpa01 — Tue, 07 Feb 2023 21:42:16 GMT

I am not understaning the Req.

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

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

afaherty — Tue, 07 Feb 2023 21:48:24 GMT

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.

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

smpa01 — Tue, 07 Feb 2023 21:54:56 GMT

afaherty PFA

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

tamerj1 — Wed, 08 Feb 2023 04:58:50 GMT

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

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

afaherty — Wed, 08 Feb 2023 13:31:37 GMT

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.

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

afaherty — Wed, 08 Feb 2023 13:32:03 GMT

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

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

tamerj1 — Wed, 08 Feb 2023 14:11:34 GMT

afaherty
Please refer to attached

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

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

afaherty — Wed, 08 Feb 2023 14:41:31 GMT

Excellent! Thank you!

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

afaherty — Wed, 08 Feb 2023 22:01:44 GMT

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?