Forum Discussion
Creating logic for a Correct or Incorrect Flag in table
Hello,
Im looking for some help in creating a flag which determines whether a question has been asnwered correctly or incorrectly.
The issues im facing is that i dont know how to make the logic of the flag, because ive got many different questions and of course they each have their own correct answers (some may have two answers needed to be correct)
Im hoping someone can provide some assistance on this in structuring the logic, and on which talble it should be on
Structure:
FACT = Contains users and their answers as well as question ID
DIM = Contains total list of questions and their corresponding ID
Many to one relationship on Question ID FACT>DIM
As you can see in the FACT table, you have lots of answers and the question ID is beside, each user has an ID given to them but is based in when they do the test, if they do it multiple times, they will appear multiple times just with a new ID.
I did have to unpivot the table in order to get rid of the structure that each question was its own column, i think i was correct in doing this.
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.
12 Replies
Hi jakaihammuda
You need to know what is considered a correct answer. Without that you can't do what you want. So the first thing is to decide what is a correct answer(s) to each question.
Phil
Hi PhilipTreacy
Firstly, thank you for responding 🙂
Yeah I have done this, however im unsure if ive done it correct. See the screenshot below and my explanationI created an asnwers table linked again through QuestionID. The thing is, with multiple choice answers, I saw it as theyd each need a seperate column in order for power bi to recognise. This is where i then beging to struggle with writing the logic because how would you (in the fact table with users and their answers) write the logic to check if the answers match (for one response asnwers) and then for two you would need to say something like 'Is in both'?
I thought of creating an answer ID on this table shows above, but then the format of the anwers within the FACT table wouldnt match this one due to the way it is formatted. I could split it by delimiter but that would bulk the table out..
Hope this is making sense in where im struggling to tie it all together
Thank you 🙂Hi jakaihammuda ,
PhilipTreacy Thanks for your concern about this case!
And jakaihammuda , I'm not sure what the answer to your question looks like, but I would suggest that you add a keywords of the answer column directly to the DIM table (or create a new table, feel free to do this) for the keywords in each response to the question (if there are more than one answer then write more than one keyword, with commas between the keywords), and then match those in Answer. After all, there are some questions where the user's answer may not be exactly the same as the correct answer, but it should be correct as well.
If keywords are all matched then return right, match part of the keywords return Lack of other answers, all do not matched then return wrong. I can give you an example.
Here is my sample data:Then please create a new Blank Query:
And put all of the M function into the Advanced Editor:
let Source = Table.NestedJoin(FACT, {"QuestionID"}, DIM, {"QuestionID"}, "DIM", JoinKind.LeftOuter), #"Expanded DIM" = Table.ExpandTableColumn(Source, "DIM", {"keywords of the answer"}, {"keywords of the answer"}), #"Lowercased Text" = Table.TransformColumns(#"Expanded DIM",{{"keywords of the answer", Text.Lower, type text}}), CustomCheck = Table.AddColumn(#"Lowercased Text", "Answer Check", each let AnswerLower = Text.Lower([Answer]), KeywordsList = Text.Split([keywords of the answer], ","), CheckList = List.Transform(KeywordsList, each Text.Contains(AnswerLower, Text.Trim(Text.Lower(_)))), AllKeywords = List.AllTrue(CheckList), AnyKeywords = List.AnyTrue(CheckList) in if AllKeywords then "Right" else if AnyKeywords then "Lack of other answers" else "Wrong"), FinalTable = Table.RemoveColumns(CustomCheck,{"keywords of the answer"}) in FinalTableThe 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.
