Solved: First attempt pass (incorporating continuation fro...

RicFischer · ‎06-05-2021

I need helping automatically updating the FirstAttemptPass column in the table below. I'm afraid my brain is not wrapping itself around the logic needed to accomplish this. If we need to create additional columns, that's fine.

In this usage, a single test is comprised of matching Oral and Practical Test Types. While a Practical may not have a matching Oral (see Kay, Sue, and Ron, below), an Oral always leads to a Practical.

The grades are Satisfactory, Unsatisfactory, and Incomplete.

The protocol is:

If Oral is Satisfactory and if Practical is Satisfactory, then FirstAttemptPass is Yes (or True).
If Oral is Incomplete, find the next occurence of the same oral and go back to step 1.
If Oral is Satisfactory and Practical is Incomplete, find the next Practical and go back to step 1.
If Oral is Unsatisfactory, then both it and the matching Practical (regardless of grade) is No (or False).
If Practical is Unsatisfactory, then both it and the matching Oral (regardless of grade) is No (or False).
If Practical has no matching Oral, as in the case with my Course 4, Stage 4, Unit 4 examples below, then a first Satisfactory is Yes, an Incomplete that leads to a Satisfactory is Yes, and an Unsatisfactory (either as first unit or as a continuation from Incomplete) is a No.

I hope I've spelled out all the conditions. If not, please feel free to ask for clarification. I've linked a .pbix file which has the same data as the table inserted below.

Sample Data for FirstAttemptPass

StudentName	ActivityStart	Course	Stage	Unit	Test Type	Grade	FirstAttemptPass
Ana	01/02/21 8:00 AM	Course 1	Stage 1	Unit 10	Oral	S	Yes
Ana	01/03/21 5:00 PM	Course 1	Stage 1	Unit 10	Practical	S	Yes
Ana	01/04/21 5:00 PM	Course 1	Stage 2	Unit 17	Oral	I
Ana	01/06/21 1:00 PM	Course 1	Stage 2	Unit 17	Oral	S	Yes
Ana	01/07/21 8:00 AM	Course 1	Stage 2	Unit 17	Practical	S	Yes
Deb	01/01/21 8:00 PM	Course 2	Stage 1	Unit 12	Oral	U	No
Deb	01/02/21 7:00 PM	Course 2	Stage 1	Unit 12	Oral	S	No
Deb	01/04/21 3:00 PM	Course 2	Stage 1	Unit 12	Practical	S	No
Deb	01/05/21 10:00 PM	Course 2	Stage 2	Unit 14	Oral	S	Yes
Deb	01/06/21 7:00 PM	Course 2	Stage 2	Unit 14	Practical	S	Yes
Sam	01/01/21 11:00 AM	Course 3	Stage 1	Unit 7	Oral	I
Sam	01/02/21 10:00 AM	Course 3	Stage 1	Unit 7	Oral	U	No
Sam	01/03/21 7:00 PM	Course 3	Stage 1	Unit 7	Oral	S	No
Sam	01/05/21 11:00 AM	Course 3	Stage 1	Unit 7	Practical	S	No
Tom	01/01/21 10:00 AM	Course 1	Stage 1	Unit 10	Oral	S	No
Tom	01/02/21 9:00 AM	Course 1	Stage 1	Unit 10	Practical	I
Tom	01/03/21 10:00 AM	Course 1	Stage 1	Unit 10	Practical	U	No
Tom	01/05/21 7:00 AM	Course 2	Stage 1	Unit 9	Oral	S	Yes
Tom	01/06/21 9:00 AM	Course 2	Stage 1	Unit 9	Practical	I
Tom	01/07/21 3:00 PM	Course 2	Stage 1	Unit 9	Practical	S	Yes
Kay	01/12/21 8:30 AM	Course 4	Stage 4	Unit 4	Practical	S	Yes
Sue	01/12/21 10:45 AM	Course 4	Stage 4	Unit 4	Practical	I
Sue	01/13/21 10:00 AM	Course 4	Stage 4	Unit 4	Practical	S	Yes
Ron	01/14/21 2:30 PM	Course 4	Stage 4	Unit 4	Practical	I
Ron	01/15/21 3:00 PM	Course 4	Stage 4	Unit 4	Practical	U	No

Fowmy · ‎06-06-2021

@RicFischer

I think I solved it, please check.

FirstAttemptPass Solved = 
VAR __o = "Oral"
VAR __p = "Practical"
VAR __grades =
    CALCULATETABLE (
        Table1,
        ALLEXCEPT (
            Table1,
            Table1[StudentName],
            Table1[Course],
            Table1[Stage],
            Table1[Unit]
        )
    )
VAR Result =
    SWITCH (
        TRUE (),
        CONTAINS ( __grades, Table1[Test Type], __o, Table1[Grade], "U" )
            || CONTAINS ( __grades, Table1[Test Type], __p, Table1[Grade], "U" ), "NO",
        CONTAINS ( __grades, Table1[Test Type], __o, Table1[Grade], "S" )
            && CONTAINS ( __grades, Table1[Test Type], __p, Table1[Grade], "S" ), "YES",
        NOT CONTAINS ( __grades, Table1[Test Type], __o )
            && CONTAINS ( __grades, Table1[Test Type], __p, Table1[Grade], "S" ), "YES"
    )
RETURN
    IF ( Table1[Grade] = "I", BLANK (), IF ( Table1[Grade] = "U", "NO", Result ) )

Did I answer your question? Mark my post as a solution! and hit thumbs up

⭕ Subscribe and learn Power BI from these videos

⚪ Website ⚪ LinkedIn ⚪ PBI User Group

View solution in original post

Fowmy · ‎06-09-2021

@RicFischer

This could work:

FirstAttemptPass Solved = 
VAR __o = "Oral"
VAR __p = "Practical"
VAR __grades =
    CALCULATETABLE (
        FirstAttemptPass,
        ALLEXCEPT (
            FirstAttemptPass,
            FirstAttemptPass[StudentName],
            FirstAttemptPass[Course],
            FirstAttemptPass[Stage]
            // FirstAttemptPass[Unit]
        ),
        FirstAttemptPass[CancelReason] = BLANK()
    )
VAR Result =
    SWITCH (
        TRUE (),
        CONTAINS ( __grades, FirstAttemptPass[Test Type], __o, FirstAttemptPass[Grade], "U" )
            || CONTAINS ( __grades, FirstAttemptPass[Test Type], __p, FirstAttemptPass[Grade], "U" ), "NO",
        CONTAINS ( __grades, FirstAttemptPass[Test Type], __o, FirstAttemptPass[Grade], "S" )
            && CONTAINS ( __grades, FirstAttemptPass[Test Type], __p, FirstAttemptPass[Grade], "S" ), "YES",
        NOT CONTAINS ( __grades, FirstAttemptPass[Test Type], __o )
            && CONTAINS ( __grades, FirstAttemptPass[Test Type], __p, FirstAttemptPass[Grade], "S" ), "YES"
    )
RETURN
    SWITCH( 
        TRUE(),
        FirstAttemptPass[Grade]= "I", BLANK(),
        FirstAttemptPass[CancelReason] <> BLANK(), BLANK(),
        FirstAttemptPass[Grade] = "U", "NO", 
        Result 
    )

Did I answer your question? Mark my post as a solution! and hit thumbs up

⭕ Subscribe and learn Power BI from these videos

⚪ Website ⚪ LinkedIn ⚪ PBI User Group

View solution in original post

RicFischer · ‎06-07-2021

When I plugged the answer into my Power BI, I realized I had forgotten my sample data was missing a few crucial fields (because, in my real data, the names of the units don't always match up between Oral and Practical). So, I'm asking for a second time in the hopes that you all will be kind enough to help me with this (again).

I forgot to mention in my first post that I needed a Column, not a measure.

The grades are Satisfactory, Unsatisfactory, and Incomplete.

The clarified protocol is:

If first Oral is Satisfactory and if first Practical is Satisfactory, then FirstAttemptPass is Yes (or True).
If Oral is Incomplete, find the next occurence of the same oral and go back to step 1.
If Oral is Satisfactory and Practical is Incomplete, find the next Practical and go back to step 1.
If Oral is Unsatisfactory, then both it and the matching Practical (regardless of date or grade) is No (or False).
If Practical is Unsatisfactory, then both it and the matching Oral (regardless of date or grade) is No (or False).
If first Practical has no matching Oral, as in the case with my Course 4, Stage 4, Unit 4 examples below, then a first Satisfactory is Yes, an Incomplete that leads to a Satisfactory is Yes, and an Unsatisfactory (either as first unit or as a continuation from Incomplete) is a No.

The critical new item is the MatchingCourseStageUnit field. Because the names of the units don't always line up (an example: Unit 9a might be Oral and Unit 9b might be Practical) in the older, now-retired courses, I had to come up with a separate matching field for lookup sanity.

Sample Data (same link as before, but file is updated)

Here's my new sample data:

StudentName	ActivityStart	Course	Stage	Unit	CourseStageUnit	OppositeCourseStageUnit	MatchingCourseStageUnit	Test Type	Grade	ManualFirstAttemptPass
Ana	01/02/21 8:00 AM	Course 1	Stage 1	Unit 10 Oral	Course 1Stage 1Unit 10 Oral	Course 1Stage 1Unit 10 Practical	Course 1Stage 1Unit 10 Practical	Oral	S	Yes
Tom	01/01/21 10:00 AM	Course 1	Stage 1	Unit 10 Oral	Course 1Stage 1Unit 10 Oral	Course 1Stage 1Unit 10 Practical	Course 1Stage 1Unit 10 Practical	Oral	S	No
Ana	01/03/21 5:00 PM	Course 1	Stage 1	Unit 10 Practical	Course 1Stage 1Unit 10 Practical	Course 1Stage 1Unit 10 Oral	Course 1Stage 1Unit 10 Oral	Practical	S	Yes
Tom	01/02/21 9:00 AM	Course 1	Stage 1	Unit 10 Practical	Course 1Stage 1Unit 10 Practical	Course 1Stage 1Unit 10 Oral	Course 1Stage 1Unit 10 Oral	Practical	I
Tom	01/03/21 10:00 AM	Course 1	Stage 1	Unit 10 Practical	Course 1Stage 1Unit 10 Practical	Course 1Stage 1Unit 10 Oral	Course 1Stage 1Unit 10 Oral	Practical	U	No
Ana	01/04/21 5:00 PM	Course 1	Stage 2	Unit 17 Oral	Course 1Stage 2Unit 17 Oral	Course 1Stage 2Unit 17 Practical	Course 1Stage 2Unit 17 Practical	Oral	I
Ana	01/06/21 1:00 PM	Course 1	Stage 2	Unit 17 Oral	Course 1Stage 2Unit 17 Oral	Course 1Stage 2Unit 17 Practical	Course 1Stage 2Unit 17 Practical	Oral	S	Yes
Ana	01/07/21 8:00 AM	Course 1	Stage 2	Unit 17 Practical	Course 1Stage 2Unit 17 Practical	Course 1Stage 2Unit 17 Oral	Course 1Stage 2Unit 17 Oral	Practical	S	Yes
Deb	01/01/21 8:00 PM	Course 2	Stage 1	Unit 12 Oral	Course 2Stage 1Unit 12 Oral	Course 2Stage 1Unit 12 Practical	Course 2Stage 1Unit 12 Practical	Oral	U	No
Deb	01/02/21 7:00 PM	Course 2	Stage 1	Unit 12 Oral	Course 2Stage 1Unit 12 Oral	Course 2Stage 1Unit 12 Practical	Course 2Stage 1Unit 12 Practical	Oral	S	No
Deb	01/04/21 3:00 PM	Course 2	Stage 1	Unit 12 Practical	Course 2Stage 1Unit 12 Practical	Course 2Stage 1Unit 12 Oral	Course 2Stage 1Unit 12 Oral	Practical	S	No
Tom	01/05/21 7:00 AM	Course 2	Stage 1	Unit 9 Oral	Course 2Stage 1Unit 9 Oral	Course 2Stage 1Unit 9 Practical	Course 2Stage 1Unit 9 Practical	Oral	S	Yes
Tom	01/06/21 9:00 AM	Course 2	Stage 1	Unit 9 Practical	Course 2Stage 1Unit 9 Practical	Course 2Stage 1Unit 9 Oral	Course 2Stage 1Unit 9 Oral	Practical	I
Tom	01/07/21 3:00 PM	Course 2	Stage 1	Unit 9 Practical	Course 2Stage 1Unit 9 Practical	Course 2Stage 1Unit 9 Oral	Course 2Stage 1Unit 9 Oral	Practical	S	Yes
Deb	01/05/21 10:00 PM	Course 2	Stage 2	Unit 14 Oral	Course 2Stage 2Unit 14 Oral	Course 2Stage 2Unit 14 Practical	Course 2Stage 2Unit 14 Practical	Oral	S	Yes
Deb	01/06/21 7:00 PM	Course 2	Stage 2	Unit 14 Practical	Course 2Stage 2Unit 14 Practical	Course 2Stage 2Unit 14 Oral	Course 2Stage 2Unit 14 Oral	Practical	S	Yes
Sam	01/01/21 11:00 AM	Course 3	Stage 1	Unit 7 Oral	Course 3Stage 1Unit 7 Oral	Course 3Stage 1Unit 7 Practical	Course 3Stage 1Unit 7 Practical	Oral	I
Sam	01/02/21 10:00 AM	Course 3	Stage 1	Unit 7 Oral	Course 3Stage 1Unit 7 Oral	Course 3Stage 1Unit 7 Practical	Course 3Stage 1Unit 7 Practical	Oral	U	No
Sam	01/03/21 7:00 PM	Course 3	Stage 1	Unit 7 Oral	Course 3Stage 1Unit 7 Oral	Course 3Stage 1Unit 7 Practical	Course 3Stage 1Unit 7 Practical	Oral	S	No
Sam	01/05/21 11:00 AM	Course 3	Stage 1	Unit 7 Practical	Course 3Stage 1Unit 7 Practical	Course 3Stage 1Unit 7 Oral	Course 3Stage 1Unit 7 Oral	Practical	S	No
Kay	01/12/21 8:30 AM	Course 4	Stage 4	Unit 4 Practical	Course 4Stage 4Unit 4 Practical	Course 4Stage 4Unit 4 Oral		Practical	S	Yes
Sue	01/12/21 10:45 AM	Course 4	Stage 4	Unit 4 Practical	Course 4Stage 4Unit 4 Practical	Course 4Stage 4Unit 4 Oral		Practical	I
Sue	01/13/21 10:00 AM	Course 4	Stage 4	Unit 4 Practical	Course 4Stage 4Unit 4 Practical	Course 4Stage 4Unit 4 Oral		Practical	S	Yes
Ron	01/14/21 2:30 PM	Course 4	Stage 4	Unit 4 Practical	Course 4Stage 4Unit 4 Practical	Course 4Stage 4Unit 4 Oral		Practical	I
Ron	01/15/21 3:00 PM	Course 4	Stage 4	Unit 4 Practical	Course 4Stage 4Unit 4 Practical	Course 4Stage 4Unit 4 Oral		Practical	U	No

RicFischer · ‎06-08-2021

If anybody can help with this, I would really appreciate it.

To be more specific, I need to modify @Fowmy's solution where it looks at a MatchingCourseStageUnit field to find the matching course (oral-to-practical or practical-to-oral) in order to determine if the entire event was a first-time-pass or not. For example:

In the first record, we have Ana's first Oral with a CourseStageUnit value of "Course 1Stage 1Unit 10 Oral". We see she passed it (Grade="S"), so we need to use the MatchingCourseStageUnit field with a value of "Course 1Stage 1Unit 10 Practical" to look up the matching unit. The matching unit would be with the same student name (Ana) and a CourseStageUnit value of "Course 1Stage 1Unit 10 Practical". If that's a Grade="S" (and it is in this case), then it's a Yes on the FirstAttemptPass calculation.

If it's a failure for either unit(Grade="U"), then both units need a "No" and all other attempts of both the Oral and Practical for the same Student, Course, and Stage need to be a "No" as well, because they didn't pass it on the first attempt.

Fowmy · ‎06-08-2021

@RicFischer

Can you try the solution that I already provided but remove the filter on the [Unit] within ALLEXCEPT.
as follows?

FirstAttemptPass Solved = 
VAR __o = "Oral"
VAR __p = "Practical"
VAR __grades =
    CALCULATETABLE (
        Sheet1,
        ALLEXCEPT (
            Sheet1,
            Sheet1[StudentName],
            Sheet1[Course],
            Sheet1[Stage]
            // Sheet1[Unit]
        )
    )
VAR Result =
    SWITCH (
        TRUE (),
        CONTAINS ( __grades, Sheet1[Test Type], __o, Sheet1[Grade], "U" )
            || CONTAINS ( __grades, Sheet1[Test Type], __p, Sheet1[Grade], "U" ), "NO",
        CONTAINS ( __grades, Sheet1[Test Type], __o, Sheet1[Grade], "S" )
            && CONTAINS ( __grades, Sheet1[Test Type], __p, Sheet1[Grade], "S" ), "YES",
        NOT CONTAINS ( __grades, Sheet1[Test Type], __o )
            && CONTAINS ( __grades, Sheet1[Test Type], __p, Sheet1[Grade], "S" ), "YES"
    )
RETURN
    IF ( Sheet1[Grade] = "I", BLANK (), IF ( Sheet1[Grade] = "U", "NO", Result ) )

Did I answer your question? Mark my post as a solution! and hit thumbs up

⭕ Subscribe and learn Power BI from these videos

⚪ Website ⚪ LinkedIn ⚪ PBI User Group

RicFischer · ‎06-08-2021

I'm getting some now that are FirstAttemptPass Solved with Yes even though it has no grade at all. No Grade is "(Blank)" in the drop-down for that field:

This happens when the event is canceled, something I didn't think of when posing the question. They should be completely ignored in this formula. They do not affect the status of a later attempt being considered as a First Attempt.

Fowmy · ‎06-09-2021

@RicFischer

Please share sample data with blank grades and the expected results .

Did I answer your question? Mark my post as a solution! and hit thumbs up

⭕ Subscribe and learn Power BI from these videos

⚪ Website ⚪ LinkedIn ⚪ PBI User Group

RicFischer · ‎06-09-2021

File updated at original link: https://drive.google.com/file/d/1OfIkdM5DbmERBG7CDbZFSsFDYA71aszY/view?usp=sharing

Notes:

This adds Joe. Joe had two cancellations, one for his oral due to a scheduling error and one for his practical due to weather. There are no grades given for cancellations. There is a CancelReason column and any text in that column means the event did not happen.
Joe's other notable item is that he's enrolled in a course where the unit names are all unique. For example, his oral is Unit 4a Oral and his practical is Unit 4b Practical. I am using a lookup table elsewhere to automatically populate a MatchingCourseStageUnit in my main table so I can see the other part of the test.
The other requirements remain the same. I have to find the first time a student passes a combined (matching) oral and practical that they've taken. If they fail the first of either the oral or the practical for that course, stage, and unit pairing, then they've failed the whole thing and didn't get a first time pass.

Thank you, again, for your help. I've been studying and learning from what you've already provided so I can do this on my own in the future.

Fowmy · ‎06-09-2021

@RicFischer

This could work:

FirstAttemptPass Solved = 
VAR __o = "Oral"
VAR __p = "Practical"
VAR __grades =
    CALCULATETABLE (
        FirstAttemptPass,
        ALLEXCEPT (
            FirstAttemptPass,
            FirstAttemptPass[StudentName],
            FirstAttemptPass[Course],
            FirstAttemptPass[Stage]
            // FirstAttemptPass[Unit]
        ),
        FirstAttemptPass[CancelReason] = BLANK()
    )
VAR Result =
    SWITCH (
        TRUE (),
        CONTAINS ( __grades, FirstAttemptPass[Test Type], __o, FirstAttemptPass[Grade], "U" )
            || CONTAINS ( __grades, FirstAttemptPass[Test Type], __p, FirstAttemptPass[Grade], "U" ), "NO",
        CONTAINS ( __grades, FirstAttemptPass[Test Type], __o, FirstAttemptPass[Grade], "S" )
            && CONTAINS ( __grades, FirstAttemptPass[Test Type], __p, FirstAttemptPass[Grade], "S" ), "YES",
        NOT CONTAINS ( __grades, FirstAttemptPass[Test Type], __o )
            && CONTAINS ( __grades, FirstAttemptPass[Test Type], __p, FirstAttemptPass[Grade], "S" ), "YES"
    )
RETURN
    SWITCH( 
        TRUE(),
        FirstAttemptPass[Grade]= "I", BLANK(),
        FirstAttemptPass[CancelReason] <> BLANK(), BLANK(),
        FirstAttemptPass[Grade] = "U", "NO", 
        Result 
    )

Did I answer your question? Mark my post as a solution! and hit thumbs up

⭕ Subscribe and learn Power BI from these videos

⚪ Website ⚪ LinkedIn ⚪ PBI User Group

RicFischer · ‎06-09-2021

Thank you, @Fowmy. I think this solution will work.

I also learned a lot from the formula. I appreciate it.

ERD · ‎06-06-2021

Hi @RicFischer ,

Do you need a measure or a column?

Here is a measure:

#_FirstAttemptPass =
VAR currentGrade = MAX ( Table1[Grade] )
VAR _t =
    CALCULATETABLE (
        VALUES ( Table1[Grade] ),
        ALLEXCEPT (
            Table1,
            Table1[StudentName],
            Table1[Course],
            Table1[Stage],
            Table1[Unit]
        )
    )
VAR result =
    IF (
        HASONEVALUE ( Table1[Grade] ),
        SWITCH (
            TRUE (),
            currentGrade = "I", "",
            currentGrade = "U", "No",
            currentGrade = "S" && "U" IN _t, "No",
            "Yes"
        )
    )
RETURN
    result

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

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

Check out my latest demo report in the data story gallery.

Stand with Ukraine!

Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!

Fowmy · ‎06-06-2021

@RicFischer

I think I solved it, please check.

FirstAttemptPass Solved = 
VAR __o = "Oral"
VAR __p = "Practical"
VAR __grades =
    CALCULATETABLE (
        Table1,
        ALLEXCEPT (
            Table1,
            Table1[StudentName],
            Table1[Course],
            Table1[Stage],
            Table1[Unit]
        )
    )
VAR Result =
    SWITCH (
        TRUE (),
        CONTAINS ( __grades, Table1[Test Type], __o, Table1[Grade], "U" )
            || CONTAINS ( __grades, Table1[Test Type], __p, Table1[Grade], "U" ), "NO",
        CONTAINS ( __grades, Table1[Test Type], __o, Table1[Grade], "S" )
            && CONTAINS ( __grades, Table1[Test Type], __p, Table1[Grade], "S" ), "YES",
        NOT CONTAINS ( __grades, Table1[Test Type], __o )
            && CONTAINS ( __grades, Table1[Test Type], __p, Table1[Grade], "S" ), "YES"
    )
RETURN
    IF ( Table1[Grade] = "I", BLANK (), IF ( Table1[Grade] = "U", "NO", Result ) )

Did I answer your question? Mark my post as a solution! and hit thumbs up

⭕ Subscribe and learn Power BI from these videos

⚪ Website ⚪ LinkedIn ⚪ PBI User Group

RicFischer · ‎06-07-2021

I forgot to specify that I needed a column. I consider @Fowmy's answer correct and will mark it as a solution. But, because the data is more complicated than I was remembering, I forgot a couple of fields. Since I recieved an acceptable solution, ~~I'll start a new thread and ask my clarified question~~ I'll write a reply to this thread because my new thread was marked as spam.

First attempt pass (incorporating continuation from incomplete grade)

Helpful resources

Microsoft Fabric & AI Learning Hackathon

How to Get Your Question Answered Quickly