Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 ...
Need Help
Show and Tell
Tips and Tricks
  • BA_Pete's avatar
    BA_Pete
    4 years ago

     

    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

BA_Pete's avatar
BA_Pete
Super User
4 years ago

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's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      BA_Pete
      Super User
      4 years ago

       

      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's avatar
        Anonymous
        Not applicable
        4 years ago

        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",