Working with survey multi-answer questions

[Anonymous]

I have finally taken the plunge and decided to use PowerBI for analysing my survey data, instead of the specialised survey analysis software.

All is very straightforward, except when dealing with multi-answer questions. These are questions where the respondent can select more than answer (for example, "Where did you see this advert" with possible answers TV, Radio, Newspaper, Magazine).

The data structure of such questions looks something like this:

Case	B01_TV	B01_Radio	B01_Newsp	B01_Mag	B01_None
1	1	1
2		1	1	1
3					1
4		1
...

What I need is to combine these so that when I plot a bar chart for example, I'll be able to rank the popularity of each medium (so sum of 1s in each column divided by the total number of Cases).

I can think of a couple of ways of achieving this, but they are very very long-winded. Does anyone know of a smart and quick way? (A typical survey can have 10+ such questions which is why I'm looking for the most efficient way.)

Many thanks in advance!

George. 

[MattAllington]

I suggest you unpivot the data to this shape

case.   Answer

1.        Tv

1.        Radio

2.        Radio

2.        News

3.        Mag

you don't need the 1s. 

You can then do a distinct count on the answer to see how many of each. 

Distinct count on Case to count the cases

divide the 2 to get an average etc

[Anonymous]

Thank you @MattAllington

This would work if these were the only data I had in the table, but I have another 30 variables or so, some of which are multi-answers like the one I described. If I unpivot all of them, the table becomes a mess and it's impossible to make any sensible calculations with the other variables.

I thought of creating a calculated table with the multi questions, but I'm not able to unpivot the columns...

[v-ljerr-msft · Accepted solution]

Hi @Anonymous,

It seems that the Unpivot option may be the best solution in this case.

---

This would work if these were the only data I had in the table, but I have another 30 variables or so, some of which are multi-answers like the one I described. If I unpivot all of them, the table becomes a mess and it's impossible to make any sensible calculations with the other variables.

---

Is it possible for you to duplicate the table, remove unnecessary columns, then unpivot the duplicated table in Query Editor? By this way, you should be able to create a new table with multi questions which is similar to create a calculate table using DAX.

Regards