Forum Discussion
Creating logic for a Correct or Incorrect Flag in table
Hi jakaihammuda ,
Since you can be sure that all questions are objective, we can use a very simple way to complete this question. I recreated the test data set:I assume that the questions are single choice, unordered multiple choice, judgment, sorting on two items and sorting on three items.
Then as before, New Source -> Blank Query:And put all of the M function into the Advanced Editor:
let Source = Table.NestedJoin(FACT, {"QuestionID"}, DIM, {"QuestionID"}, "DIM", JoinKind.FullOuter), #"Expanded DIM" = Table.ExpandTableColumn(Source, "DIM", {"CorrectAnswers", "IsOrder"}, {"CorrectAnswers", "IsOrder"}), #"Added Custom" = Table.AddColumn(#"Expanded DIM", "Custom", each if [IsOrder] = 0 then if [Answer] = [CorrectAnswers] then "Right" else if Text.Contains([CorrectAnswers], [Answer]) and not Text.Contains([Answer], [CorrectAnswers]) then "Lack of other answer" else "Wrong" else if [Answer] = [CorrectAnswers] then "Right" else if Text.Length([Answer]) = Text.Length([CorrectAnswers]) then "Wrong order" else "Lack of other answer"), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"IsOrder", "CorrectAnswers"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"UserID", "Answer", "Custom", "QuestionID"}) in #"Reordered Columns"The final output is as below:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hey Anonymous ,
So yes. All of the questions that are being worked with are objective. The users have to select the choices (max 2 in some cases) or just one result. There is no free text out of these answers.
However there is just the one question where they have to re order tiles to display the correct order or process steps, so technically there is only one correct answer, so would be the equivalent as a one choice selection being correct or incorrect.
The only issue i found was that, specifically with questions that have a "select 2". the users can select the correct two answers, but the order in whihc they select them first depends on the display order in the data/sharepoint file where it gets saved. as ou can see in the screenshot, its not a problem because i have 2 correct aswer columns (1 and 2) to cater for either or of the combinations (as there can only be max 2 combinations). however its not ideal i did that, i only did so becaause it kept it easier to work with. For example if we added a question where they had to select 3 asnwers to be correct, the options of order they can select them in increases, thus meaning id have to create even more correct answer columns to cater for all possibilties. woudlnt be ideal, but i only did that because i dont think the questions will be changing.
does this help make it more clear for you? 🙂
thank you
Hi jakaihammuda ,
Since you can be sure that all questions are objective, we can use a very simple way to complete this question. I recreated the test data set:
I assume that the questions are single choice, unordered multiple choice, judgment, sorting on two items and sorting on three items.
Then as before, New Source -> Blank Query:
And put all of the M function into the Advanced Editor:
let
Source = Table.NestedJoin(FACT, {"QuestionID"}, DIM, {"QuestionID"}, "DIM", JoinKind.FullOuter),
#"Expanded DIM" = Table.ExpandTableColumn(Source, "DIM", {"CorrectAnswers", "IsOrder"}, {"CorrectAnswers", "IsOrder"}),
#"Added Custom" = Table.AddColumn(#"Expanded DIM", "Custom", each if [IsOrder] = 0 then
if [Answer] = [CorrectAnswers] then "Right"
else if Text.Contains([CorrectAnswers], [Answer]) and not Text.Contains([Answer], [CorrectAnswers]) then "Lack of other answer"
else "Wrong"
else
if [Answer] = [CorrectAnswers] then "Right"
else if Text.Length([Answer]) = Text.Length([CorrectAnswers]) then "Wrong order"
else "Lack of other answer"),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"IsOrder", "CorrectAnswers"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"UserID", "Answer", "Custom", "QuestionID"})
in
#"Reordered Columns"
The final output is as below:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.