Forum Discussion

WBscooby's avatar
WBscooby
Icon for Helper III rankHelper III
5 years ago
Solved

Structuring data for survey analysis

Hi   I'm quite new to Power BI and hoping for some advice on the best way to structure/ model data for survey analysis.   I have a survey of approximately 90 single response multi choice question...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi WBscooby ,

     

    First go to query editor>select column"Person ID"and "Survey ID">"Unpivot other columns":

    Create a relationship as below:

    Then create a calculated column and a measure as below:

     
     
    Is 1? = IF('survey table'[Value]="1",1,0)
    Measure = IF(SUMX(FILTER(ALL('survey table'),'survey table'[Question Number]=MAX('survey table'[Question Number])),'survey table'[Is 1?])>=1,1,0)
     
    Finally you will see:
     
    For the related .pbix file,pls see attached.
     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

AlB's avatar
AlB
Icon for Community Champion rankCommunity Champion
5 years ago

Hi WBscooby 

Can you describe a bit more the analysis that you would like to do on the data?  What visuals, etc..

The text below is not clear enough

Ultimately, I would like to analyse the data by question number and by question subset ie. if there is a 1 in any of the health questions, then 1, otherwise 0.

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers