Graph showing the total count of values based on multiple selection source from microsoft forms

[Anonymous]

Hello, I Have made a form on microsoft forms and I am using its data on a PBI. There is this one question set to allow "multiple selections" so when the data is imported into PBI it looks like this:

By default on the responses tab of microsoft forms I get the following graph that shows the total count of each value like so 

I want to replicate this graph in PBI and change it to a donut graph but since the fields contain multiple values when I set then as legend values I get the combination of values instead of the sum of each individual one like so :

How can I plot the same graph as in microsoft forms ? 
Thanks in advance

[AlexisOlson · Accepted solution]

You need to split the lines with multiple values into multiple rows before loading the data into your model.

You can do this in the query editor using the Split Column by Delimiter with the Advanced option Split Into Rows as I show in the first step in this post.

[FrankAT · Accepted solution]

Hi @Anonymous ,

with DAX you can do it like this. Take a look at the attached PBIX file.

[Anonymous]

@FrankAT First thank you for your help, but there is an issue with that code. As we can see using the same example that you fowarded me the count is always off by 1 lets tak for instance "Marketing" it shows up in "nos diga quais..." 4 times but the count shows only 3 of them. As well for all other instances.

the easy fix here is for me to add 1 the the final count but I've been trying to wrap my head around what the code is doing so I can learn new things or even tweak the code to fix this issue, but no luck here... I am pretty new to DAX, can you give an explanation of what is hapenning and maybe a tweak to fix the afore mentioned issue?

what i got from the code is: We are creating an auxiliary table using DAX that is receiving one column from the main table and adding another one that counts the number of elements in a string (I can't exactly figure out how using lengh is doing this). Then we generate a column form 1 to 10 (is there a reason to stop at 10 ?) then we filter this virtual table and add a colum to it that fills in the job description (couldn't figure out why it works either).

Thank you in advance

[FrankAT]

Hi @Anonymous ,

the three blue marked cells holds the same values delimited by semicolon. The DAX function PATHITEM() splits the values by their denoted position 1, 2, 3  - listed in column Value.

The green marked column Job is the final rsesult which is used in the report.

Read the whole explanation of the DAX code here:

http://sqljason.com/2013/06/split-delimited-row-into-multiple-rows.html

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[Anonymous]

Thank you so much for your time, I,ll try this out! but ended up splitting and unpivotting it through powerquery but I rather nor have and aditional tabel just for a single graph, so I'll use this solution in a future update to the report.

[AlexisOlson · Accepted solution]

You need to split the lines with multiple values into multiple rows before loading the data into your model.

You can do this in the query editor using the Split Column by Delimiter with the Advanced option Split Into Rows as I show in the first step in this post.