Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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.
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.
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.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
42 | |
33 | |
30 | |
18 | |
18 |