Forum Discussion

EMason's avatar
EMason
Frequent Visitor
7 months ago
Solved

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 ...
  • EMason's avatar
    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]
    
        )
    )