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...
Need Help
  • 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?

Ashish_Mathur's avatar
Ashish_Mathur
Super User
5 years ago

Hi,

With disconnected tables this should be possible to solve.  Share some data to work with.

sfmike99's avatar
sfmike99
Advocate II
5 years ago

Great - here's a sample with four questions and ten respondents. (source table is > 3m rows):

https://1drv.ms/x/s!AvmRZllooYiOnyuQ3NNFa7laXSL9?e=giUbQ9

 

If I understand your comment, then treating this data as two different tables would enable any intersection to be calculated - letting you have two differents slicers for the same data. But how would that work with the measure?

 

Thanks for looking at this.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

    What is the use of the RowIndex column?  Not only do i fund that confusing, i see that it has the same values as those shown in the Value column.  Don't we need just the first 3 columns to answer your question?

    • sfmike99's avatar
      sfmike99
      Advocate II
      5 years ago

      Ah sorry - I see where that is confusing. 

       

      There are multiple question types in this dataset, of which this is one example. The meaning of the Value field varies according to the question type. It could be a 0/1 or a ranking for example. For simplicity here I just included one type where the Value and RowIndex are the same. 

       

      The purpose of RowIndex is a lookup for the specific response option chosen, while value contains their response (which may or may not be the same). For this sample data you can ignore one of them.