Powerbi analyze survey questions. Filter one visual shows answers of the selected attendees.

[CBO2404]

I made a survey in Forms and load it into powerBI. 
The table I made has 3 columns: ID, Question and Answer. See picture below. 
In my example, I want to be able to click "Man" in the Gender visual. 
What I want to see in the other 2 visuals is all the answers to other questions filled in by a man. 
When I try, the other visuals becomes empty. 

Does anyone know how I can do this filtering without filter on question only on ID??

[halfglassdarkly · Accepted solution]

The easiest way to achieve this would be to structure your table differently by pivoting your Q+A columns in PowerQuery so each question / answer appears in its own column. That way filtering e.g. on Gender will include all records with that gender, not just the records where the question is Gender as in your current table structure.

See: https://support.microsoft.com/en-us/office/pivot-columns-power-query-abc9c8da-3be9-44c4-886e-0be331a... 

[CBO2404]

thanks for your quick reply.
Only this does not work on questions with more than 1 answer option.
Then for every question of that type I have to make an extra table with a relationship on ID (both ways)
Or do you have any suggestion for this problem?
I have about 100 questions and 1/3 may have more than 1 answer option.....
A lot of work......:-(

 

[halfglassdarkly]

You might be able to cheat and use the original (un pivoted) table to filter the pivoted table by ID, instead of needing seperate filter tables for each multi answer question? However you may still run into issues with which table to use for which visual if you need each user selection to filter all the other visuals.

[CBO2404]

Then I get the same problem as in the beginning 🙂