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. 

  • sfmike99's avatar
    sfmike99
    Advocate II
    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?

    • v-kelly-msft's avatar
      v-kelly-msft
      Community Support
      5 years ago

      Hi  sfmike99 ,

       

      Could you pls upload your .pbix file if there' s no confidential information?It would be beneficial for test.

       

      Best Regards,
      Kelly

      Did I answer your question? Mark my post as a solution!

       

      • sfmike99's avatar
        sfmike99
        Advocate II
        5 years ago

        Thanks v-kelly-msft but I think I'm good. This was a really fun project to work on, and an interesting alternative to crosstab / matrices. Did a demo for the client today and they loved it. 

        Also I had an aha this morning. Instead of duplicating the large unpivoted dataset (3m+ rows) I think I can just duplicate the Question/Response table (already used for reports) and join both to the data. The two slicers should then work to produce two different views of the same underlying data.

        LMK if anyone has any questions.