Forum Discussion
Unpivoted Survey Responses Cohort Matching - Visual Filters based on response
- 6 months ago
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] ) )
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_Mathur7 months ago
Super User
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-karpurapud7 months ago
Community Support
Hi EMason
Thank you for reaching out to the Microsoft Fabric community forum.
I have implemented solution using sample data to demonstrate the correct cohort analysis approach, where selecting a specific Question and Answer identifies a subset of submissions and recalculates their responses across the remaining questions.
The PBIX file attached for your review. Please take a look and let me know your observations or any feedback you may have.
If you have any more questions, please let us know and we’ll be happy to help.
Regards,
Microsoft Fabric Community Support Team.