Forum Discussion
How to evaluate test results from multiple students?
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.
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 chgTypesApply 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
9 Replies
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.
No problem Steph, any time.
Best of luck with your studies 🙂
Pete
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
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.
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
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
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 chgTypesApply 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
