How to deal with survey data with multiple answers

[AlexeyKW]

Hi all!

I have an issue with modeling survey data in Power BI. I have a survey of abour 80 questions, with some underquestions, that my company does every year. We used to outsource the report before, but it was terrible and not very user friendly.

The survey contains single answer and multiple choice questions, and also some conditional extra questions.

I have modeled the data like this:
DimRespondent (contains RespondentPK, demographics)

DimQuestion (contains QuestionPK, QuestionNumber, QuestionName, UnderquestionName)

DimAnswerAlternative (contains AnswerAlternativePK, AnswerAlternativeName)

FactSurvey (contains RespondentFK, QuestionFK, AnswerAlternativeFK)

The Dims are connected to the fact with the PK/FK fields.

Now to the issue. My goal is to be able to see what respondents who answered Q1: Do you enjoy the product? (Q1A) "Yes, I enjoy the product" also answered to other questions visualised on the slide.

Each slide has 4-6 bar charts visualising one question each that I need to be cross-filtering each other. However, because of how my model is, I need to filter each bar chart with QuestionNumber or QuestionName. This makes the charts unable to cross-filter, making an analysis of the findings an exercise in futility.

This cannot be a unique issue for me, so I'm wondering if anyone has experience with survey data, and how you've modeled it to allow cross-filtering across questions.

Thank you in advance!

[Anonymous]

Hi @AlexeyKW ,

Whether your problem has been resolved? If no, could you please share a simplified pbix file with us just as suggested by danextian? You can refer the following link to share it:

How to upload PBI in Community

Best Regards

[AlexeyKW]

Apologies for the late reply.
Here is a pbix with dummy data:
https://drive.google.com/file/d/1Sh6UU_SGXnOKHMEy3jr332MUTJxat7An/view?usp=sharing 

[Anonymous]

Hi @AlexeyKW ,

Based on your initial description, your data model consists of four tables. Your goal is to create interactive charts to analyze the survey data. However, the file you shared contains only one table. Are you aiming to identify which respondents answered each question and also determine the other questions answered by those respondents?

To assist you further, please refer to the following resources for comprehensive guidance on analyzing survey data:

Survey – DAX Patterns

PowerPoint Presentation

Visualize complex survey data in Power BI - YouTube

Best Regards

[danextian]

This is how I would want the factsurvey to be laid out. This format makes it easy to slice and dice the data.

[AlexeyKW]

Thanks for the reply!
I tried this (my model is basically the same, but with keys and codes, mostly because some of the answers are VERY long, and there are 15K respondents.
Unfortunately, that model runs into the same issue: by filtering a visual for one question, you're effectively hindering it from cross-filtering other visuals.

[danextian]

Please expound on this. Unfortunately, that model runs into the same issue: by filtering a visual for one question, you're effectively hindering it from cross-filtering other visuals.

Also, a sanitized copy your pbix with sensitive information removed would be very helpful.

[AlexeyKW]

Apologies for the late reply!
Here is a pbix with dummy data, very small but it's pretty much exactly what you described and what I have now. As you can see, it isn't possible to cross-filter between bar charts.
https://drive.google.com/file/d/1Sh6UU_SGXnOKHMEy3jr332MUTJxat7An/view?usp=sharing