cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
SimonStriegel Frequent Visitor
Frequent Visitor

Multiple codes in one cell - options for analysis

Dear Community, 

 

I am trying to analyze a survey with PowerBI that has a number of Multiple Choice questions, whereas each response option is associated with an ID (1,2,3,...; 1=bus, 2=car etc.). My database is built in a way, that all IDs selected for a single questionnaire a stored in one single cell in the format: 

 

                                               Column

Questionnaire ID  1                1,2,4,8

Questionnaire ID  1                2,5,7

...

 

I would like to create a report which counts the different IDs, i.e. how many times is "1", "2", "3" (...) mentioned and visualize that in a simple bar diagramm. However, all option I tried so far did not really work as I had hoped. 

 

Is there any simple solutions to this? I would be very grateful for any help. 

 

Thanks a lot!

Simon 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team
Community Support Team

Re: Multiple codes in one cell - options for analysis

@SimonStriegel,

 

You may select Split Column By Delimiter > Advanced options > Split into Rows in Query Editor.

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

Re: Multiple codes in one cell - options for analysis

@SimonStriegel,

 

You may also try SEARCH in DAX.

https://community.powerbi.com/t5/Desktop/Aggregating-data-values/m-p/588290#M278966

 

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Community Support Team
Community Support Team

Re: Multiple codes in one cell - options for analysis

@SimonStriegel,

 

You may select Split Column By Delimiter > Advanced options > Split into Rows in Query Editor.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
SimonStriegel Frequent Visitor
Frequent Visitor

Re: Multiple codes in one cell - options for analysis

Thanks!

I tried that but the effect is that the number of lines in my table increases, which in turn increases the count for any data analyses (i.e. I have ca. 500 surveys, but due to the multiplication of questionnaire IDs, PowerBI implies that I have much more than these 500 surveys. And this might even skew the analyses I am running, if I am not mistaken. Any solution to this as well?

 

Best wishes

Simon 

Community Support Team
Community Support Team

Re: Multiple codes in one cell - options for analysis

@SimonStriegel,

 

You may also try SEARCH in DAX.

https://community.powerbi.com/t5/Desktop/Aggregating-data-values/m-p/588290#M278966

 

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