Forum Discussion

sfmike99's avatar
sfmike99
Advocate II
5 years ago
Solved

Using Intersect function

I have a table of survey data with four fields of interest:  Respondent: unique identifier for each participant QuestionRoot: fieldname to link data with questions RowIndex: an index to the speci...
  • sfmike99's avatar
    sfmike99
    5 years ago

    Armed with the suggestion for disconnected tables and some head scratching time, I think I figured this out.

     

    Solution was to clone the data table (QuestionA and QuestionB) and give each of them separate slicers for the questions and response options. I use DAX to create calculated tables based on the current selection for each, and use Intersect to get the numbers I need. The calculation works like this:

    • Numerator: everyone who chose selected question/response options for BOTH questions
    • Denominator: everyone who chose selected question/response for A and ANY response for B question

    Here's the DAX: 

    PctRespAthenB = 
        VAR ThisResponseA = CALCULATETABLE(
            DISTINCT(QuestionA[Respondent]),
            ALLEXCEPT(QuestionA, QuestionA[QuestionRoot], QuestionA[RowIndex])
        )
        VAR ThisResponseB = CALCULATETABLE(
            DISTINCT(QuestionB[Respondent]),
            ALLEXCEPT(QuestionB, QuestionB[QuestionRoot], QuestionB[RowIndex])
        )
        VAR AllResponseB = CALCULATETABLE(
            DISTINCT(QuestionB[Respondent]),
            ALLEXCEPT(QuestionB, QuestionB[QuestionRoot])
        )
    RETURN
       COUNTROWS(INTERSECT(ThisResponseA, ThisResponseB)) /
       COUNTROWS(INTERSECT(ThisResponseA, AllResponseB))

     

    And here's how it looks in a rough version of report. I will swap in text for questions & responses later but you get the idea.

     


    I tested this against some crosstab matrices I created before and the numbers lined up. Nifty that you can even select multiple response options to essentially bin the data.

    Make sense? Any suggestions?