How to use power BI to display multiple responses question for both filter and chart?

[HelloYanyan07]

Hi everyone, 

I'm new to power bi. I work on survey data. There will be quite a lot of multiple responses data. It will be one option one column and use 0 and 1 to display value which 0 means not selected while 1 means selected.

for example, a question A is "which fruit do you like? " Option list will be 1. apple, 2, banana, 3 others. You can select more than one option for question A.

then data will be listed as below column A1 to indicate apple, A2 to indicate banana and A3 to indicate others.

so, in data, i'll have 3 columns for question A, 6 columns for question B and 10 columns for question C.

In power Bi, i'd like to use A and B as filter and draw bar chart for C as below.

We tried to use Unpivot to achieve this function, however, it makes the whole file become slow, becuase we have 9000 rows of data, and actual multiple responses has 32 options x 25 options x 27 options.

Kindly advice any idea about how to achieve this function in more effective way.

Thank you for everyone.

[v-angzheng-msft · Accepted solution]

Hi, @HelloYanyan07 

What does C1=1, C1=5, etc. mean in the picture above? Is it the total number of A and B equal to 1? Is the bar chart showing the percentage of the number of C1=1 in the A and B conditions, i.e., the maximum is C1=9?

Based on the structure of your data, the Unpivot comes easily to mind, would transpose work as well?

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-angzheng-msft · Accepted solution]

Hi, @HelloYanyan07 

What does C1=1, C1=5, etc. mean in the picture above? Is it the total number of A and B equal to 1? Is the bar chart showing the percentage of the number of C1=1 in the A and B conditions, i.e., the maximum is C1=9?

Based on the structure of your data, the Unpivot comes easily to mind, would transpose work as well?

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HelloYanyan07 · Accepted solution]

yes, unpivot is workable, but the data is too huge after unpivot. 

C1=1 means this filter is to filter when C1=1. Same as C5=1.

Thank you for your reply, i found one article online and trying it. I'd like to share to you as well.

https://www.sqlbi.com/articles/creating-a-slicer-that-filters-multiple-columns-in-power-bi/