How to calculate/score question answers

Hi KimberlyHM ,

I think the first thing you need to do is unpivot your answers table, so each of the questions is in a single column, the same as your scoring table.

You now need to create a match on question between the answers and the scores tables. You can either add a conditional column to the answers table, like 'if questionName = "blahblah" then "Q1"... so that you can match Q1 to Q1, Q2 to Q2 etc. or you can adjust the question names in your answers table so that "Communication Skills" = "Communication Skills" etc.

Now load both tables to the data model and relate the answer table to the scores table either on scores[Index Number] = answers[newQnumberColumn] or scores[Attribute] = answers[newQnameColumn], depending on which method you chose to match the tables.

Once related, you can write measures across the tables for your scoring etc.

For example:

_totalScore =
SUMX(
  answersTable,
  (answersTable[Value] = "Yes") * RELATED(scoresTable[Weighted score])
)

Pete