How to evaluate test results from multiple students?

[Anonymous]

I have a process that works with a small data set.  But it doesn't scale .... so there must be a better way.  I hope the experts in this group can help.  The data below is a representative subset to demonstrate the use case.  The real data has 1500 rows in the answer key and 500 students.... 

I have an table that represents the answer key. 

I have an table that represents the responses received from several students.

What is the best way to develop the pass/fail score for the students?

What I did was this:

1) create a query to generate a unique list of students (S1, S2, S3)

2) use a cross join with the AnswerKey Table to generate an ExpandedKey Table  that looks like this  

and

3) do full outer join between the ExpandedKey Table and the Student Answers to achieve this... I can't do a simple join, because the student reponses may have missed answers (i.e. S3 did not answer Q4 or Q5)

From here it is easy enough to compare the Answer columns and determine the student's score.

BTW - just to clarify, it is not enough for me to simply calculate & report the student score.  I need to give them a report that shows their response and the answer.... so I need to produce a report that looks like this:

As I said at the outset, this example works fine... but doesn't scale to serve my environment.  Is there a better way for me to do this so that I don't need to cross join and a full outer join?  Is there some way to loop through and analyze the answers received from S1 and calculate the score, and then analyze the answers from S2 and then S3 etc?

Thank you.  I appreciate your guidance.

[Anonymous]

@BA_Pete  - Thank you for sharing the solution... This experience has taught me that I have a lot more to learn.  For the past year or so, I've been creating charts and transforming the data model using the tool presented in the buttons on the ribbon to calculate what I need.  I need to return to my online course and study the section on Measures and Calculated columns and get much deeper knowledge of all the functions that are in M & DAX.  Thanks for sharing the code.  Now all I need to do is modify the table/column names to suite and switch out the source of my data.  You have been a wonderful coach.  I'll reply again again after I have made the modifications and confirmed that the solution scales to meet the data I need to use.  You've been a great assistance and coach.

[BA_Pete]

No problem Steph, any time.

Best of luck with your studies 🙂

Pete

[BA_Pete]

Hi @Anonymous ,

Apply both your test table and the correct answers table to your data model.

Relate test[question] to correctAnswers[question].

Add custom column in the test table like this:

..score = 
IF(test[answer] = RELATED(correctAnswers[answer]), 1, 0)

From here you should be able to perform any calculations you want using your new [..score] column.

If you need to do a percentage, then divide by COUNTROWS(correctAnswers), something like this:

_scorePct =
DIVIDE(
    SUM(test[..score]),
    COUNTROWS(correctANSWERS),
    0
)

I get the following output:

Pete

[Anonymous]

@BA_Pete  - Thanks for the suggestion and the formula.  I've ammended the description of the problem.  calculating the score is not a challenge... it is producing the result that is the challenge.  I need to produce a report for each student that shows their score vs the answer key.    I will use bursting capabilities to generate each report, but I need the report to look something like this.

BTW Thanks for the suggestion regarding the relate capability.  I see that I have much to learn regarding DAX.

Stephanie

[BA_Pete · Accepted solution]

Aah, gotcha. Sorry.

Try this instead:

// Call this 'stephAnswers'
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKjRU0lEyVIrVATKNgMwkCNMYyCwpKk2F8ExAEjmJydkQrimQa2mpFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [question = _t, answer = _t])
in
    Source

// Call this 'stephTest'
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKjZU0lEqBBGGSrE6ML4RkEhC4hsDiZKi0lQkIROQkpzE5GwkMVMgYWkJETBCM9cIzVwjmLlpiTnFqUhiIIPLMzJLkMVABhsaQE0yRjPZGM1kY1QXxwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [student = _t, question = _t, answer = _t]),
    addStudentQ = Table.AddColumn(Source, "studentQ", each Text.Combine({[student], [question]}, "-"), type text)
in
    addStudentQ

// Call this 'stephRootTable'
let
    Source = Table.Distinct(Table.SelectColumns(stephTest, "student")),
    addPossQuestion = Table.AddColumn(Source, "possQuestion", each stephAnswers[question]),
    expandPossQuestion = Table.ExpandListColumn(addPossQuestion, "possQuestion"),
    addStudentQ = Table.AddColumn(expandPossQuestion, "studentQ", each Text.Combine({[student], [possQuestion]}, "-"), type text),
    chgTypes = Table.TransformColumnTypes(addStudentQ,{{"possQuestion", type text}})
in
    chgTypes

Apply all three to your model and relate as follows:

stephAnswers[question] > stephRootTable[possQuestion]

stephTest[studentQ] > stephRootTable[studentQ]

Add a new custom column in stephRootTable as follows:

..score = IF(RELATED(stephTest[answer]) = RELATED(stephAnswers[answer]), 1, 0)

Chuck it all in a table as follows:

This should be super-scalable as you're combining lower-resource functions of Power Query with the power of relationships to avoid costly PQ joins.

Pete

[Anonymous]

@BA_Pete - quick question... what is the long multicharater text after after FromText?  Where does this come from?  Why the reference to a Json document? What is being decompressed?  all the data is coming from spreadsheets... Thx.

FromText("i45WKjZU0lEqBBGGSrE6ML4RkEhC4hsDiZKi0lQkIROQkpzE5GwkMVMgYWkJETBCM9cIzVwjmLlpiTnFqUhiIIPLMzJLkMVABhsaQE0yRjPZGM1kY1QXxwIA",

[BA_Pete]

Hi Steph,

This is just how Power Query represents data that has been created using the 'Enter Data' function.

It stores it as a text representation of a JSON binary so that it's very quick and easy to share built tables just by copying and pasting the M code. In scenarios such as this, where I want to share a complete, working, solution with someone on a forum, it allows me to send a complete table in text form, and allows you to just paste it wholesale into a blank query using Advanced Editor and have a complete table ready to use.

Once you've pasted all the tables into PQ at your end, named them as suggested, and tested that my proposed solution works as required, you would then swap out the whole Source step from these queries and replace it with the Source step(s) that point to your actual data.

For example, if I paste the whole of the first code segment into Advanced Editor, like this:

When I hit 'Done', I get this, a fully-formed and formatted table of example data:

Pete

[ddpl]

Hi,

please try this,

Step1:

Merge both the table as per in the basis Key column as Answers

Step2: Then create conditional column

Step3: Then Group by Student name

Now you can create custom column for %

And same you can add one more custom column stating Pass or Fail

[Anonymous]

Vipul - Thanks for the suggestion works fine to calculate the score... BTW, Thanks for introducing me to the group by command.  That is awesome and I will used it for other applications.

My challenge is that it is not enough to calculate a score.  I need to show the results and the comparison... thus I can't do an inner join, I need to do an outer join..... 

I need to show this is my final report to the student 

With the inner join all I get it this

Thanks for the tip... but I'll need to try something else.