Forum Discussion

RicFischer's avatar
RicFischer
Icon for Helper I rankHelper I
5 years ago
Solved

First attempt pass (incorporating continuation from incomplete grade)

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 addit...
Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

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

     

     

  • Fowmy's avatar
    Fowmy
    5 years ago

    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 
    )
Fowmy's avatar
Fowmy
Icon for Super User rankSuper User
5 years ago

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

 

RicFischer's avatar
RicFischer
Icon for Helper I rankHelper I
5 years ago

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's avatar
    Fowmy
    Icon for Super User rankSuper User
    5 years ago

    RicFischer 

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



    • RicFischer's avatar
      RicFischer
      Icon for Helper I rankHelper I
      5 years ago

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

       

      Notes:

      1. 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.
      2. 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.
      3. 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's avatar
        Fowmy
        Icon for Super User rankSuper User
        5 years ago

        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 
    )