Survey data: filtering response options based on a common reponse ID

[BridgetEllen]

Hi, Fairly new user here.  I have a data sheet for survey data that has three columns: 1) Respondent ID, 2) Question ID, and 3) Response. Something like this:

Respondent ID	Question ID	Response
101	1	Strongly agree
101	2	Agree
101	3 (comment)	I love this class
102	1	Disagree

I have created a stacked bar chart for one of the items and want the user to be able to filter qualitative comments by clicking on response options (e.g., show all open-ended comments for every respondent who selects Strongly agree).  My problem is that since this is all in the same table, I need to somehow show comments based on Respondenent  ID.  Is this possible without separating the comments into their own table? 

I appreciate any help!

[BridgetEllen]

Hi @v-jianboli-msft ,

Thank you for your suggestion.  If I'm understanding it correctly, your approach has worked for me when I have two separate related tables linked by a common key.  For instance, I have a slicer created for individual department which works dynamically with ratings and comments.  In the case of these two variables which are in the same sheet, if I click on a response all the comments disappear and vice-versa. The common link is the respondent ID but I don't know how to designate this relationship. Sorry, I also don't quite have the language to ask the question clearly.  

Thanks again for your suggestion!

Bridget

[v-jianboli-msft]

Hi @BridgetEllen ,

Sorry for that the information you have provided is not making the problem clear to me. Can you please share more details to help us clarify your scenario?

Please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

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

[BridgetEllen]

@v-jianboli-msft,

I'll do my best to clarify--I am so new to this that it is hard to frame my question 🙂  I have survey data that I have unpivoted.  So I only have three columns in the table with all the survey data: 1) The respondent ID, 2) The question ID, and 3) The response

I have created a stacked bar chart for one of my questions.  My filters work just fine with a separate demographic file linked by respondent ID.  My struggle is I want to be able to show comments for respondents with a specific response, e.g., a user could click on the portion of the bar for strongly disagree and see all the comments from the respondents making that selection.  Currently, when I test it, if I click on a response all the comments disappear because they are in the same table on separate rows. I'm hoping there is a way to tell PowerBI to use that first column (Respondent ID) as a reference.  If Respondents 101, 102, 103 select strongly disagree on Q1 show 101, 102, 103 comments for Q2. 

I hope this is a bit clearer. 

Thank you again!

Bridget

[v-jianboli-msft]

Hi @BridgetEllen ,

You can achieve this by creating a slicer and a table visual in Power BI. Here's a step-by-step guide:

1. Create a slicer visual for the response options (e.g., Strongly agree, Agree, etc.).
2. Click on the slicer visual and go to the Format tab (the paint roller icon).
3. Now, when you click on a response option in the slicer, the visual will show the open-ended comments for respondents who selected that option.

Output:

Best Regards,

Jianbo Li

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