Changing data structure in Questionnaire data

[Anonymous]

Hi, 

I have a table built around the answers from a customer survey. the data is modelled in a way which makes it difficult to report on. My end goal is to get a stacked column chart that looks like this:

 and in excel I used count if functions in a table here. 

but the data looks like this where the answers are in separate columns ( I have also included sample data in excel).

My issue is as they are all in separate columns I can't get the counts based on the answers - I tired to pivot the columns into a new table but the first error was it only gave me the un-pivot option then when I tired to create a relationship said it was a many to many when I tried to link them back.

As always any assistance will be greatly appreciated! 

Dobby Libr3

link to sheet Test Survey Data 

[Icey]

Hi @Anonymous ,

Is this problem solved?

If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If not, please let me know.

Best Regards
Icey

[Anonymous]

Hi @Icey , 

Thanks for your help with this! I managed to get it to work, I couldn't recreate your Dax code but I managed to change the sheets around so I didn't have to. Finished dashboard below (some of the colours may change) 

Sorry for the delay in getting back to you I was pulled off this report to focus on other business crital reports. 

Thanks again

Dobby Libr3 

[Icey · Accepted solution]

Hi @Anonymous,

Try this:

PBIX file is attached.

Best Regards,
Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Icey, 

Thanks for you time! Unfortunately this wont work as a final solution as I need to keep the other columns in the table so the slicers will work, I have slicers for [Area:] and [Building Master] and [Start Date]. I should have mentioned this in the original post sorry! 

Thanks Dobby

[Icey]

Hi @Anonymous ,

I think it works. If it is not what you want, please let me know.

Best Regards,

Icey