Creating logic for a Correct or Incorrect Flag in table

[jakaihammuda]

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

[PhilipTreacy]

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

[jakaihammuda]

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 explanation

I 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 🙂

[v-junyant-msft]

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
    FinalTable

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.

[jakaihammuda]

Hi @v-junyant-msft 

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!

[jakaihammuda]

@v-junyant-msft 

just as a fyi. I replaced the ";" with "&" like so

[v-junyant-msft]

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 Tao

[jakaihammuda]

Hi @v-junyant-msft 

Much appreciated thank you very much! I look forward to you response 🙂

Take care in the meantime!

Jakai

[v-junyant-msft]

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
    FinalTable

And 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.

[jakaihammuda]

Hi, @v-junyant-msft 

Thank you so much for helping on this!

I think im almost there now but just the one issue regarding the "keywords", this isnt optimal for me to use based on the fact that some of the keywords may appear in some other question answers that are wrong. Its best if it soley do it on the correct answer as opposed to keywords.

This is my structure.

FACT:
Contains the user, results ID, time, Answers and Question ID

DIM table (Correct Answer):

DIM table (Question)

I still want that logic or Correct, partially correct, Incorrect that youve created but i just cant base it off keywords 😞 the partially correct logic can still apply right? using the "&" adn checking on words before/after that to see if its a match with any in the actual correct answer?

Is much amending needed to be done to just base it off the actual corrcet asnwer only?

[v-junyant-msft]

Hi @jakaihammuda ,

I used keywords because I wasn't really sure what type of question your topic was.
If you have a lot of subjective questions, i.e. you need the user to organize his/her own answers, then there may be cases where the user's response is not exactly the same as your standard answer, but it is still correct, and then if you use the standard answer exclusively, then this type of response will be flagged as an error.
However, if all your questions are objective, such as (multiple) choice or judgment questions, i.e., you can guarantee that the answer provided by the user is word-for-word the same as your standard answer, then it doesn't matter. But if all the questions are objective, then maybe the method I provided before will be a bit tricky, because I use field matching, and there should be a simpler way to do it.

So I still have to ask you to confirm that you can guarantee that your users' answers can be made to look exactly like your standard answers or not.

Best Regards,
Dino Tao

[jakaihammuda]

Hey @v-junyant-msft ,

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

[v-junyant-msft · Accepted solution]

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.