Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 ID | Name |
001 | Jo |
002 | Dave |
003 | Brian |
004 | Paul |
005 | Alan |
Question Number | Question Subset |
1 | A |
2 | A |
3 | A |
4 | B |
5 | B |
6 | B |
7 | C |
8 | C |
9 | C |
10 | C |
Person ID | Survey ID | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
001 | 101 | 3 | 3 | 2 | 0 | Yes | Yes | Yes | 0 | 3 | Yes |
002 | 102 | 2 | 3 | 2 | 3 | No | No | No | 3 | 4 | No |
003 | 103 | 3 | 0 | 3 | 3 | Yes | Yes | Yes | 3 | 1 | Yes |
004 | 104 | 0 | 2 | 1 | 3 | Yes | Yes | Yes | 3 | 1 | Yes |
005 | 105 | 1 | 2 | 4 | 0 | No | No | No | 3 | 3 | No |
Solved! Go to Solution.
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:
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
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:
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!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |