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] ) )
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]
)
)