Survey analysis: selection/filtering respondents based on responses

[Anonymous]

I have a simple dataset in PowerBI from a survey. 80 respondents filled out a 13 question survey with a Likert-scale answer model (strongly disagree to strongly agree). Data is presented in a 14 column (ID-column, and 13 question columns) and 80 rows.

Because I thought I might need it, I have unpivotted the table too, so there is an ID-column, a question column (values Q01-Q13) and a  response column. I also have a labels-table so I can sort my answers in the right order and provide them with appropriate labels in my visuals.

I can plot the data in a stacked bar chart:

X-axis is a counting measure ofr responses, the Y-axis is based on the Questions-column in the unpivot table, the legend is based on the labels table.

I want to analyse certain selections from the responses based on a particular answer to a particular question. For example I want to see which respondents have answered "strongly agree" to Q01, and see how they filled the rest of the survey. Basically filtering my respondents as if one would do in the table view:

And then have my visuals be updated with whichever question and/or answer I choose to zoom in on.  Unfortunately I have not find a solution (yet) on this forum and I every advise i get from chatgpt leads to syntax errors. I have a feeling this should be a really easy fix, but I've now spend the best part of two days probably looking in all the wrong places for advise. 

Would love to hear what I could do to make this work. Thanks!

[Anonymous]

Dear Leroy,

This is amazing. Thank you so much! With a few adjustments to table and column names, I got it to work to my pivotted table and now everything is running smoothly!

[Anonymous]

Hi there,

Yes, I have tried a couple of slicer combinations, the most logical one being:

Unfortunately, using this slicer, and clicking on one answer, it limits the visual to just the one answer to that question:

If I reverse the hierarchy in the slicer, I get all "agree" responses to a particular question. But I'm looking for the group of respondents who answered (for example) "Agree" to question 3, and I want to see how those respondents replied to all other questions. Much like a "(all) customers who bought X also bought Y" kind of comparison

[Anonymous · Accepted solution]

Thanks for the reply from ray_aramburo , please allow me to provide another insight:

Hi, @Anonymous 

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2.Then I created a calculation table like this, and used it as a slicer:

select111 = SELECTCOLUMNS('Table','Table'[question],'Table'[response])

3. Below are the measure I've created for your needs:

MEASURE =
VAR response1 =
    SELECTEDVALUE ( 'select111'[Table_response] )
VAR ques1 =
    SELECTEDVALUE ( 'select111'[Table_question] )
VAR ID1 =
    CALCULATETABLE (
        VALUES ( 'Table'[ID] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[question] = ques1
                && 'Table'[response] = response1
        )
    )
RETURN
    IF (
        ISFILTERED ( select111 ),
        CALCULATE (
            COUNT ( 'Table'[response] ),
            FILTER ( 'Table', 'Table'[ID] IN ID1 )
        ),
        COUNT ( 'Table'[response] )
    )

4.Then replace the counting measure for responses with the measure:

5.Here's my final result, which I hope meets your requirements.
 

 

Please find the attached pbix relevant to the case.

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ray_aramburo]

The easiest solution is to unpivot your data. You should aim for a 3 column structure which is ID-Question-Answer.

[Anonymous]

I have done so, as written in the original post. I had a feeling the key to the answer might have been in there, and I guess I may need to work with lists and variables or something but I'm not sure how to go from there

[ray_aramburo]

Ah you're right. The image mislead me and thought you had a column per question. Have you tried using a hierarchy slicer (Question + Answer)? You could use some combination of that + restricting interactions with another table so you can see the whole picture in one table plus the user in another, and/or also edit the interactions to filter the detailed table based on a click(selection) of the user visual table.