Filter for a Field with Multiple Values per User

[Anonymous]

I'm working on a dataset with survey responses and building a number of filters based on the user responses. Respondent data, answer data, answer options data, and questions data are all in their own tables, with IDs used to link them together.

The relevant tables here are answers and respondents.

In two of the questions, multiple options can be chosen. In this case, each RespondentId has multiple rows in the answers table, one for each answer.

I'm trying to create a filter that would list all the chosen sports (excluding the open text field seen on row 4 above) and filter the charts based on the sports selected. So if you'd select either Yoga or Golf from the filter, the graphs would show the answers for user 38366134, and anyone else who had chosen those sports.

I've had no issues creating these filters for questions where each user has a single answer (essentially I've been adding columns to the respondents table with appropriate data), but with these multiple choice ones I'm stuck.

I have done a measure 

SportsDone = CALCULATE(VALUES(answers[AnswerText]), FILTER(answers, 'answers'[QuestionId] = 672894))

, but haven't been able to figure out a way to actually put this into use.

Any tips?

[v-kelly-msft · Accepted solution]

Hi  @Anonymous ，

First  create a slicer table as below:

slicer table = VALUES('Table'[QuestionId])

Then create a measure as below:

Measure = 
var _tab=CALCULATETABLE(VALUES('Table'[Answer Text]),FILTER(ALL('Table'),'Table'[QuestionId]=SELECTEDVALUE('slicer table'[QuestionId])))
Return
IF(MAX('Table'[Answer Text]) in _tab,MAX('Table'[Answer Text]),BLANK())

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@Anonymous , I am not very clear with question.

Count of question for which all users have given same answer(free text is not removed)

a new measure 

countx(filter(summarize(answers, 'answers'[QuestionId] , calculate(countdistinct(answers[AnswerText]))), [_1]=1),[QuestionId])