Forum Discussion
RicFischer
Helper I
5 years agoFirst 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...
- 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 ) ) - 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
Super User
5 years agoRicFischer
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
) RicFischer
Helper I
5 years agoThank you, Fowmy. I think this solution will work.
I also learned a lot from the formula. I appreciate it.