Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Changing data structure in Questionnaire data

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 

 

 

 

Need Help
  • Icey's avatar
    Icey
    6 years ago

    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.

     

     

5 Replies

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Icey
        Community Support
        6 years ago

        Hi Anonymous ,

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

         

         

        Best Regards,

        Icey

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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