Unpivoted Survey Responses Cohort Matching - Visual Filters based on response

[EMason]

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:

Spoiler

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!

[EMason · Accepted solution]

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]

    )
)

[v-karpurapud]

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.

[danextian]

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.

[EMason]

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

1. 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) ✔️
2. See the answer distribution as you have in the "count of Submission ID by Question and Answer" bar chart in the pbix. ✔️
3. Click on a part of that barchart to select a cohort e.g. All people for Q3 who said "No"  ✔️
4. 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:
   1. Q1: 33% said "No", 67% said "Yes"
   2. Q2: 100% said "Yes"
   3. Q4: 33% said "No", 67% said "Yes"
   4. 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!

[v-karpurapud]

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.

[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.