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 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!
Anonymous
just as a fyi. I replaced the ";" with "&" like so
Hi jakaihammuda ,
It's almost the end of the week, I'll try to keep testing it as you requested and I'll reply in time if I have another better solution!
Best Regards,
Dino TaoHi Anonymous
Much appreciated thank you very much! I look forward to you response 🙂
Take care in the meantime!Jakai
Hi jakaihammuda ,
Back for work!
First of all on the issue of separators, whether you choose to use "," or ";" or "&" are not relevant, you just need to modify the code in this place:Note, however, that as far as possible, only one separator is used in this column, and it may be troublesome to use multiple separators, such as both commas and semicolons.
Also based on this screenshot of yours, I see that you have spaces between the & and the character, so I'm afraid you need to count the two spaces before and after the & as separators as well.Then regarding the order issue, my suggestion is that you can add another new column Isorder in the DIM table and mark it as 1 if there is an order issue with the answers to the current question, otherwise mark it as null. as shown below:
In the FACT table I added some sample data:
Then 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.FullOuter), #"Expanded DIM" = Table.ExpandTableColumn(Source, "DIM", {"keyword", "Isorder"}, {"keyword", "Isorder"}), CustomCheck = Table.AddColumn(#"Expanded DIM", "Answer Check", each let AnswerLower = Text.Lower([Answer]), lowerKeyword = Text.Lower([keyword]), KeywordsList = Text.Split(lowerKeyword, ","), CheckList = List.Transform(KeywordsList, each Text.Contains(AnswerLower, Text.Trim(Text.Lower(_)))), AllKeywords = List.AllTrue(CheckList), AnyKeywords = List.AnyTrue(CheckList) in if [Isorder] = 1 then let CheckAnswer = (QuestionID as number, keyword as text, Answer as text) => let keywordList = Text.Split(lowerKeyword, ","), checkOrder = List.Accumulate( keywordList, [FoundAll = true, LastIndex = 0, WrongOrder = false], (state, currentKeyword) => let currentIndex = Text.PositionOf(AnswerLower, currentKeyword, Occurrence.First), isCurrentFound = currentIndex <> -1, isNewIndexBigger = currentIndex > state[LastIndex], isWrongOrder = state[WrongOrder] or (isCurrentFound and not isNewIndexBigger), hasFoundAllSoFar = state[FoundAll] and isCurrentFound in [ FoundAll = hasFoundAllSoFar, LastIndex = if isCurrentFound then currentIndex else state[LastIndex], WrongOrder = isWrongOrder ] ), result = if not checkOrder[FoundAll] then "Lack of other answers" else if checkOrder[WrongOrder] then "wrong order" else "right" in result in CheckAnswer([QuestionID], [keyword], [Answer]) else if AllKeywords then "Right" else if AnyKeywords then "Lack of other answers" else "Wrong"), FinalTable = Table.RemoveColumns(CustomCheck,{"keyword"}) in FinalTableAnd 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.