Help with measure: Questionnaire report - connect answers

[michaelsh]

Hello friends,

I have a simple Question-Answer table. It has ID field that defines a person who answered, also one question can have multiple answers.

I want to show on my dashboard visuals with answers - separate visual for every question.

But I want it to be interactive, such that when a user clicks on an answer for any certain question, the other visuals will show what answers were given by these same people for other questions. 
So, for example, if I click on 14 as an answer to "How old are you" question, I want the visual of "What Pets do you like" to show the following answers: 2 Dogs, 1 Cats, and 1 Birds, because these answers were given by the two sudents that answered "14".

I feel that I can do it with a measure, or maybe to change a model somehow.

Please help.

Thanks

[v-xiaotang]

Hi @michaelsh 

Thanks for reaching out to us.

If you want the visuals to show values related to the value you click in the "How old are you" visual, the value you click should be column not measure. I create a sample for your reference, hope it helps!

Best Regards,

Community Support Team _Tang

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

[michaelsh]

@v-xiaotang 

First of all, that you so much for taking the time!!! I really appreciate it!

I see what you've done here:

- Created calculated tables for each question

- Made both-directional filter
- Created specific measures for each question using ALLEXCEPT

That works exactly like I wanted it to, so that's great!

Nevertheless, my challenge is to create only ONE smart generic measure and without creating separate tables for each question. I'll explain:

My actual model based on a questionnaire with more than 30 questions. And questions constantly may be added. So I don't want to create a table and a measure for each question.

I will have to add visuals filtered on each question, but inside them I want to use a generic smart measure that "listens" to what other visuals are clicked on and shows the results accordingly. 
I know it is challenging, that's why I published this qustion here.

I will very much appreciate if you can help me with this.

[v-xiaotang · Accepted solution]

Hi @michaelsh 

Thanks for your reply, and thanks for your kudo!

But just I mentioned above, create only ONE smart generic measure and without creating separate tables for each question is not possible, because the value you click should be a column from a separate table not measure. 

The reason why these should be column from a separate table is, the other 3 table will disappear if you use the column from same table.

For example,

Best Regards,

Community Support Team _Tang

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

[amitchandak]

@michaelsh , Not very clear. But seems like first visual should interact only with the second top and the second top should interact with the rest 3 but not the 1st one https://docs.microsoft.com/en-us/power-bi/service-reports-visual-interactions

[michaelsh]

The visual on the left is just showing the raw data.

I need all other visuals to interact.

It is not a simple interaction, the measure has to be very smart.

My question is about how to build a measure