New column based off values in two different related tables

[jennibelle22]

I've got a data set of survey results and I'm struggling a bit with a particular new column creation that will help me conduct correlation analysis between the answers to different questions in the survey. I've tried out a few of the solutions I found in this forum, but they haven't worked thus far (details below). I'm pretty sure I'm missing something basic in how my data model is set up or the filter functions I'm using.

Data Model:

Not every question is the same format (some Likert scales, some Y/N, some frequency scales, that sort of thing), hence the slightly greater complexity to the data model. I can change the model, but haven't figured out a more useful set of relationships to change it to.

'SurveyResults'		'Questions'		'QuestionTypes'
[RespondentID] [Question] [Response] [NewColumn]	Linked by Many to One [Question]	[Question] [QuestionCategory] [QuestionType] [PositivityIndex]	Linked by Many to Many [QuestionType]	[QuestionType] [ResponseOptions] [ResponseValue]

'Questions'[PositivityIndex] is a +/- 1 value based on the phrasing of the question. Eg. some questions if you 'strongly agree' that's a good thing relating to a +1, and some that's a bad thing so the index would be -1.

'QuestionTypes'[ResponseValue] is a decimal based on the response. Eg. for a Likert style question the response 'strongly agree' would have a larger value than 'agree'.

What I'm trying to create is a new column in the 'SurveyResults' table that assigns a numerical value to each response based on the combination of the relevant PositivityIndex and ResponseValue so that I have a numerical for each individual response, rather than just the text 'Strongly Agree'.

I tried the following:

NewColumn = related( 'QuestionTypes'[ResponseValue]) * related('Questions'[PositivityIndex])

It errored and said the column either doesn't exist or doesn't have a relationship to any table available in the current context. Then I realised duh, that doesn't give a link at all to 'SurveyResults'[Response] so of course it didn't come up with anything.

After a bit of searching around I tried filtering more explicitly and just getting a clear link to the ResponseValue to start with rather than the full calculation:

NewColumn = calculate( related('QuestionTypes'[ResponseValue]), 'QuestionTypes'[QuestionType] = 'Questions'[QuestionType], 'QuestionTypes'[ResponseOptions] = 'SurveyResults'[Response])

Got the same error, so I fiddled around with related functions and got nowhere.

I'm pretty comfortable when I'm dealing with just two tables and reasonably simple calculations, but the relationships in this one have stretched my basic DAX skills, alas. Help?

[VahidDM · Accepted solution]

HI @jennibelle22 

Try this:

NewColumn =
VAR QuestionType = RELATED('Questions'[QuestionType])
VAR PositivityIndex = RELATED('Questions'[PositivityIndex])
VAR ResponseValue = LOOKUPVALUE(
    'QuestionTypes'[ResponseValue],
    'QuestionTypes'[QuestionType], QuestionType,
    'QuestionTypes'[ResponseOptions], 'SurveyResults'[Response]
)
RETURN
    IF(
        NOT(ISBLANK(ResponseValue)),
        ResponseValue * PositivityIndex,
        0  // or another default value
    )

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn|Twitter|Blog |YouTube 

[jennibelle22]

Thank-you!!