Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
WBscooby
Helper III
Helper III

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 questions. Each question also has a subset eg Q1-3 - Health, Q 4-7 - Accommodation etc. 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.

 

I currently have 3 tables - Respondent table, question table and survey table - dummy, simplified versions below. The survey is currently in tabular format. I'm wondering if this is the best format for analysis or if I should be unpivoting the data? I get a little confused with the relationships if I unpivot since there will then be multiple person IDs. Or is it easiest to create extra column with if statements? It seems like I have a lot of columns already!

 

Any advice would be much appreciated!

Thank you!

 

Person IDName
001Jo
002Dave
003Brian
004Paul
005Alan

 

Question NumberQuestion Subset
1A
2A
3A
4B
5B
6B
7C
8C
9C
10C

 

Person IDSurvey ID12345678910
0011013320YesYesYes03Yes
0021022323NoNoNo34No
0031033033YesYesYes31Yes
0041040213YesYesYes31Yes
0051051240NoNoNo33No
1 ACCEPTED SOLUTION
v-kelly-msft
Community Support
Community Support

Hi @WBscooby ,

 

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

Screenshot 2020-12-14 153610.png

Create a relationship as below:

Screenshot 2020-12-14 153742.png

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:
Screenshot 2020-12-14 154354.png
 
For the related .pbix file,pls see attached.
 

Best Regards,
Kelly

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

View solution in original post

3 REPLIES 3
v-kelly-msft
Community Support
Community Support

Hi @WBscooby ,

 

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

Screenshot 2020-12-14 153610.png

Create a relationship as below:

Screenshot 2020-12-14 153742.png

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:
Screenshot 2020-12-14 154354.png
 
For the related .pbix file,pls see attached.
 

Best Regards,
Kelly

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

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

AlB
Super User
Super User

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 

SU18_powerbi_badge

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.