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.
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.Hi Anonymous
This makes alot of sense! Thank you
Couple points to add onto this just to update you with what i found/what going to do and see if this would work the same.
My first point being that, when the aswers come in for one question and they do contain multiple words/choices for the total answer. They are split by a semi colon ";". see below
So are you saying that i could do some transofmation, replacing the semi colons entirely and splitting each word by comma instead, then that will still work for powerbi to search for seperate keywords in that field?
Makes sense, however i do have just one question that requires the answer to be in the exact right order. Something i noticed is that when a user answers a ultiple choice question (we only have a multiple choice of selecting max 2) the order depends on whatever one they select first.
This eludes to my next point, what i decided to do was this (hoping it would work too?)Create a new table with the Question ID and then either the absolute correct asnwer (based on either its a one response or two part), but to cover the order issue, have a secod column with the answer order flipped. You can see what i mean in the image
This way, could i not write the logic to be Answer1 OR Answer2 to be correct?
With this though (see question ID 9 where it needs to be the exact order answer), i wouldnt know how to create the "lack of full asnwers" feature that you made say if they selected only two of them in the correct order, say first and last place. I feel like that would need to be its own logic in itseld? and i really like you "lack of other answers" feature.
Would you recommend still getting rid of the ";"...
Thi type of logic is abit of out my expertise so your assistance on this would be so much help 🙂
Thanks!
