Business context
A healthcare charity supporting the community uses an anonymous survey to assess the efficacy of their programs and offerings.
Challenges
- You’ll usually have a very wide table of survey result, and questions that have variable format of answers, multiple parts or and multiple concatenated responses must be handled.
- The column headers of your raw results table are very long because the questions are highly detailed and have multiple parts.
- The team managing the survey and data have low capability and experience with data modelling, analysis and Power BI data visualisation.
- The existing BI solution was complicated and difficult to maintain for the low-capability team and the data model was not optimised to support ongoing enhancements to the survey.
Enhanced analysis
Additionally, analysis of qualitative responses dynamically to gain specific insights is not always straightforward. If you want to analyse positive (or negative) outcomes by other facts such as concurrent conditions, not simply by basic demographics, you need to ensure your data is modelled correctly to facilitate this and utilise advanced DAX to ensure your measures produce accurate results.
Model Approach
The approach for modelling the survey data to support this enhanced analysis involves carefully planning the assessable outcomes required to be measured and splitting the data out from the single source table into dimensions and facts in a star schema.
- Assign each survey question to a specific domain or outcome topic, against which program efficacy is assessed – these were the qualitative questions and responses, in this case an ‘agree’ or ‘disagree’ which can be assigned a quantitative value for analysis.
- Separate supplementary information such as secondary conditions or therapeutic strategies from basic respondent demographics.
- Create Facts and Dimensions with these groupings
Data comes from source as a single table so significant transformations in power query were necessary to clean and prepare the raw data for a final model as below.
Analytical Approach
The cardinality and filter direction of the star schema mean that we can’t automatically analyse outcomes by concurrent conditions or applied therapeutic strategies.
So answering questions like: “Which combination of co-morbidities have poor outcomes?” and subsequently identifying which applied strategies could help improve outcomes for those people is not as easy as simply filtering the list of respondents or filtering for a specific condition using slicers on the page.
- Base measure: Calculating the number of respondents a. # Responses = CALCULATE ( DISTINCTCOUNT ’Fact Reponses’[RespondentID])
- Intermediate measure: calculate the base measure filtered for only positive/negative outcomes (in our case, agree or disagree) a. # Agreed = CALCULATE ( [# Responses], ’Fact Reponses’[Response] = “Agree” )
- Final result: Calculate the agree / disagree responses using summarisations of another fact table a. # Agreed with conditions = CALCULATE ( [# Agreed], SUMMARIZE( ‘Fact Respondent Strategies’, ‘DIM Respondent’[RespondentID])
The ‘base’ calculation can be whatever the primary metric for analysis is, such as growth or percent of total.
Using the ‘3. Final result’ measure in your visuals, you can then use standard dimensional slicers on the page to create different combinations of conditions and therapies to find the most or least effective groupings.
Benefits of this approach
- This method of using a summarised fact table in DAX to push the filtering from one fact to another, not only allows client to report on basic patient outcomes, but it helps uncover dynamic and layered insights to improve the charities’ ability to recommend targeted therapies and supplementary pain management strategies.
- Identify programs that should be phased out or improved
- Be able to target marketing and outreach to new and existing participants
- The model can easily handle changes such as updating survey questions, creating additional outcome groupings and adding new questions to the survey.
Other applicable scenarios
- Comparing student exam results with the study methodologies used, and different combinations of subjects or learning differences
- Analyse property value growth/decline by specific combinations of property features When survey data is messy, wide and packed with qualitative responses, insights get lost.
By transforming a single raw survey source into an optimised dimensional model and leveraging advanced DAX to bridge fact tables, we unlock layered insights that connect outcomes to co-morbidities, therapies and demographics; driving smarter, evidence-based decisions.
One51 Consulting can help you design scalable, future-ready analytics solutions. Contact us to see how we can transform your data into actionable intelligence.
Ready to transform messy survey data into actionable insights?
Get in touch to explore how we can support your Power BI journey.
