Forum Discussion

WBscooby's avatar
WBscooby
Helper 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!

v-kelly-msft's avatar
v-kelly-msft
Community Support
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!

WBscooby's avatar
WBscooby
Helper III
5 years ago

Thank you, this was just what I needed. Sorry for delayed response - other work priorities took over!