Forum Discussion
Unpivoted Survey Responses Cohort Matching - Visual Filters based on response
Hi all,
In essence, my question is this:
Is it possible to have a bar chart with responses that I can cohort match by clicking on the visual, and have the same visual update to show the cohorts answers to the rest of the questions?
If not, what could be a good way to visually show a cohorts answers that can be easily done by someone non-technical?
Details below.
I have a very large, unpivoted table as my fact_table that contains responses to surveys. I also have a number of dimensions such as questions, answers, dates etc.
Here is a basic replication of the fact_table (that works without the dimensions):
A large part of what is wanted from the dashboard is the ability to select responses and see how that cohort answered other questions. The Submission ID is the ID relating to the submission of a survey, hence, letting us know what other questions the user answered.
See the following image as a basic example using a wide table approach:
In the Q1 visual, the '5' part of the bar is selected and it highlights/filters the other bar charts.
Is it possible to have this kind of interactivity in an unpivoted table as well as not having to make a new chart for every question? We have a lot of surveys of differing sizes and being able to select a survey from a slicer and see all of the questions while still being able to interact would be amazing.
Non-working, unpivoted table version
Explanation of why the above does not currently work, from my understanding:
When I click on "Yes" for Q1, it filters the bar chart visual for both that answer and question, in other words, making everything else blank as each answer is on its own row in the fact_table, which is filtered out due to not matching question & answer.
Table with data for example:
| User ID | Survey ID | Submission ID | Question | Answer | Score |
| 1 | 1 | 1 | Q1 | Yes | 5 |
| 1 | 1 | 1 | Q2 | Yes | 5 |
| 1 | 1 | 1 | Q3 | No | 0 |
| 1 | 1 | 1 | Q4 | No | 0 |
| 2 | 1 | 2 | Q1 | Yes | 5 |
| 2 | 1 | 2 | Q2 | Yes | 5 |
| 2 | 1 | 2 | Q3 | Yes | 5 |
| 2 | 1 | 2 | Q4 | Yes | 5 |
| 3 | 1 | 3 | Q1 | Yes | 5 |
| 3 | 1 | 3 | Q2 | No | 0 |
| 3 | 1 | 3 | Q3 | Yes | 5 |
| 3 | 1 | 3 | Q4 | No | 0 |
| 4 | 1 | 4 | Q1 | Yes | 5 |
| 4 | 1 | 4 | Q2 | Yes | 5 |
| 4 | 1 | 4 | Q3 | Yes | 5 |
| 4 | 1 | 4 | Q4 | Yes | 5 |
| 5 | 1 | 5 | Q1 | No | 0 |
| 5 | 1 | 5 | Q2 | Yes | 5 |
| 5 | 1 | 5 | Q3 | No | 0 |
| 5 | 1 | 5 | Q4 | Yes | 5 |
| 6 | 1 | 6 | Q1 | Yes | 5 |
| 6 | 1 | 6 | Q2 | Yes | 5 |
| 6 | 1 | 6 | Q3 | Yes | 5 |
| 6 | 1 | 6 | Q4 | Yes | 5 |
| 7 | 1 | 7 | Q1 | Yes | 5 |
| 7 | 1 | 7 | Q2 | No | 0 |
| 7 | 1 | 7 | Q3 | Yes | 5 |
| 7 | 1 | 7 | Q4 | Yes | 5 |
| 8 | 1 | 8 | Q1 | Yes | 5 |
| 8 | 1 | 8 | Q2 | Yes | 5 |
| 8 | 1 | 8 | Q3 | No | 0 |
| 8 | 1 | 8 | Q4 | Yes | 5 |
| 1 | 2 | 9 | Q1 | Yes | 3 |
| 1 | 2 | 9 | Q2 | Maybe | 2 |
| 1 | 2 | 9 | Q3 | No | 1 |
| 1 | 2 | 9 | Q4 | Yes | 3 |
| 1 | 2 | 9 | Q5 | Yes | 3 |
Thanks so much for taking the time to read this. Let me know if I can explain anything further!
Thanks to all who replied, especially v-karpurapud who came the cloests to what I was looking for.
I came up with a solution that works for me and allows me to dig into cohorts.
Here is the model view:
Here is it in action:
Without filters
Filtering for cohort who said "Yes" to Q1
Filtering for cohort who said "Yes" to Q1 AND said "No" to Q2
How it works
The key is having the slicer be the "Disconnected" tables.
Then, in the measure, filter the fact table to get all rows that have any of the selected questions & answers. Then, select the Submissions that have the exact number of Questions (as those Submissions have all selected answers matching).
Measures:
Screen Count = DISTINCTCOUNTNOBLANK('Fact'[Submission ID])Cohort Count = // The currently selected answers in the disconnected table (selected from the slicer) VAR SelectedAnswers = VALUES ( 'Disconnected Answers'[Answer ID] ) // The questions that are selected (from the slicer) VAR SelectedQs = VALUES ( 'Disconnected Questions'[Question ID] ) VAR WantedSubmissions = // Select the Submission IDs SELECTCOLUMNS( // Filter the table to only include Submissions with the exact number of questions required FILTER( // Group by submission id and get the distinct count of questions SUMMARIZE( // Get all fact rows that contain the selected answer ids CALCULATETABLE( 'Fact' ,ALL('Fact') ,TREATAS(SelectedAnswers,'Fact'[Answer ID]) ) ,'Fact'[Submission ID] ,"QCount", COUNTA('Fact'[Question ID]) ), [QCount] = COUNTROWS(DISTINCT(SelectedQs)) ) ,'Fact'[Submission ID] ) RETURN // If statement as [Screen Count] returns blank with ,TREATAS(WantedSubmissions, 'Fact'[Submission ID]) when nothing is selected. IF( OR(ISFILTERED('Disconnected Answers'),ISFILTERED('Disconnected Questions')) ,CALCULATE( [Screen Count] ,TREATAS(WantedSubmissions, 'Fact'[Submission ID]) ) ,CALCULATE( [Screen Count] ,'Fact'[Submission ID] ) )
6 Replies
- danextian
Super User
I am unclear of what you want to achieve but assuming for the same submission, you want to know how a user answered other questions, you can try the attache pbix.
Thanks for replying! I think I have explained poorly.
It's not the individual submissions that I need to look at. I need to look through hundreds of thousands. What I'm looking for is
- a way to look at a specific subset of questions that are in a survey (I can select the Survey ID to filter the questions) ✔️
- See the answer distribution as you have in the "count of Submission ID by Question and Answer" bar chart in the pbix. ✔️
- Click on a part of that barchart to select a cohort e.g. All people for Q3 who said "No" ✔️
- Have this cohort's answers for all other question's be shown (I have put a visual showing what I would like this to look like further down) ❌
e.g. For all people who answered "No" for Q3:- Q1: 33% said "No", 67% said "Yes"
- Q2: 100% said "Yes"
- Q4: 33% said "No", 67% said "Yes"
- etc
My idea for this is to get the submission ID's of the selected cohort as I can create a subset of the fact table where those submissions exist (with all questions and answers for the given survey id). I was mainly wondering if it is possible to have the same visual filter when I click on the cohort or if it will always only highlight what I have clicked (see images below)
Image showing no highlighting for other bars:
Here is what I had come up with (can't share the pbix as it is with my actual data). On the first visual, I have clicked on the 4th question's score of 1 and the second visual is highlighting how that cohort answered.
Is there any way to have this behaviour with just the single visual? Or perhaps a nicer way of displaying what I already have?
Thanks again!
- Ashish_Mathur
Hi,
I am not sure how much can help but i would like to try. Could you please share some sample data to work with and show the result in a simple table format.
v-karpurapudCommunity Support
Hi EMason
I wanted to check if you’ve had a chance to review the information provided. If you have any further questions, please let us know. Has your issue been resolved? If not, please share more details so we can assist you further.
Thank You.Thanks to all who replied, especially v-karpurapud who came the cloests to what I was looking for.
I came up with a solution that works for me and allows me to dig into cohorts.
Here is the model view:
Here is it in action:
Without filters
Filtering for cohort who said "Yes" to Q1
Filtering for cohort who said "Yes" to Q1 AND said "No" to Q2
How it works
The key is having the slicer be the "Disconnected" tables.
Then, in the measure, filter the fact table to get all rows that have any of the selected questions & answers. Then, select the Submissions that have the exact number of Questions (as those Submissions have all selected answers matching).
Measures:
Screen Count = DISTINCTCOUNTNOBLANK('Fact'[Submission ID])Cohort Count = // The currently selected answers in the disconnected table (selected from the slicer) VAR SelectedAnswers = VALUES ( 'Disconnected Answers'[Answer ID] ) // The questions that are selected (from the slicer) VAR SelectedQs = VALUES ( 'Disconnected Questions'[Question ID] ) VAR WantedSubmissions = // Select the Submission IDs SELECTCOLUMNS( // Filter the table to only include Submissions with the exact number of questions required FILTER( // Group by submission id and get the distinct count of questions SUMMARIZE( // Get all fact rows that contain the selected answer ids CALCULATETABLE( 'Fact' ,ALL('Fact') ,TREATAS(SelectedAnswers,'Fact'[Answer ID]) ) ,'Fact'[Submission ID] ,"QCount", COUNTA('Fact'[Question ID]) ), [QCount] = COUNTROWS(DISTINCT(SelectedQs)) ) ,'Fact'[Submission ID] ) RETURN // If statement as [Screen Count] returns blank with ,TREATAS(WantedSubmissions, 'Fact'[Submission ID]) when nothing is selected. IF( OR(ISFILTERED('Disconnected Answers'),ISFILTERED('Disconnected Questions')) ,CALCULATE( [Screen Count] ,TREATAS(WantedSubmissions, 'Fact'[Submission ID]) ) ,CALCULATE( [Screen Count] ,'Fact'[Submission ID] ) )
