Create a single column based on multiple columns and conditions

[MekdigG]

Hi,

I need to create a column (a Status) to show list of clients who has completed or not completed a quiz.

I have a sample table below. I want to say if a client has passed all the quizzes then "complete" if not "not complete"

there is "not applicable" value(option) in any of the columns but it shouldn't affect the output; it can be Pass or not applicable and they can be counted as completed. 

Please help 🙂

Quiz   1	Quiz   2	Quiz    3	Status
Fail	Pass	Pass	Not Completed
Pass	Fail	Pass	Not Completed
Pass	Pass	Pass	Completed
N/A	Pass	Pass	Completed
Pass	N/A	Pass	Completed
Fail	Fail	N/A	Not Completed

[AUaero · Accepted solution]

I'm assuming your quiz data has a client ID field.  If so, try this:

# Quiz Status = 
VAR Quiz1Result = MAX(QuizData[Quiz1])
VAR Quiz2Result = MAX(QuizData[Quiz2])
VAR Quiz3Result = MAX(QuizData[Quiz3])

VAR Quiz1Status = 
SWITCH(
    TRUE(),
    Quiz1Result IN {"N/A", "Pass"}, 1,
    0
)

VAR Quiz2Status = 
SWITCH(
    TRUE(),
    Quiz2Result IN {"N/A", "Pass"}, 1,
    0
)

VAR Quiz3Status = 
SWITCH(
    TRUE(),
    Quiz3Result IN {"N/A", "Pass"}, 1,
    0
)

RETURN
SWITCH(
    TRUE(),
    Quiz1Status + Quiz2Status + Quiz3Status = 3, "Complete",
    "Not Complete"
)

which will give you the following result:

Please accept this as a solution if it solves your problem.

[MekdigG]

Thank you, i tried it and it gave me a new column with all results "complete", i checked to make sure the formula is correct but it is still giving me all all complete status only.

[AUaero]

Did you create this as a measure or a calculated column?  The code above will work as a measure.  To use it for a calculated column, ditch the MAX function on the QuizXResult variables.

[MekdigG]

Yes, it was created it as a column and now its working after i changed it to a meausre; thank you so much! I have accepted your solution.