topic Re: Using Intersect function in Desktop
https://community.powerbi.com/t5/Desktop/Using-Intersect-function/m-p/1707575#M676699
<P>Armed with the suggestion for disconnected tables and some head scratching time, I think I figured this out.</P><P> </P><P>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:<BR /><BR /></P><UL><LI>Numerator: everyone who chose selected question/response options for BOTH questions</LI><LI>Denominator: everyone who chose selected question/response for A and ANY response for B question</LI></UL><P>Here's the DAX: </P><LI-CODE lang="markup">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))</LI-CODE><P> </P><P>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.</P><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="sfmike99_1-1615158234333.png" style="width: 400px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/468865i7BDE9F436E3B3E3A/image-size/medium?v=v2&px=400" role="button" title="sfmike99_1-1615158234333.png" alt="sfmike99_1-1615158234333.png" /></span></P><P><BR />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.<BR /><BR />Make sense? Any suggestions?</P>Sun, 07 Mar 2021 23:05:47 GMTsfmike992021-03-07T23:05:47ZUsing Intersect function
https://community.powerbi.com/t5/Desktop/Using-Intersect-function/m-p/1701820#M675232
<P>I have a table of survey data with four fields of interest: </P><UL><LI>Respondent: unique identifier for each participant</LI><LI>QuestionRoot: fieldname to link data with questions</LI><LI>RowIndex: an index to the specific response chosen for that question</LI><LI>Value: the individual response to that question/response pair (0 or 1)</LI></UL><P>We have created a dashboard to allow exploration of the data. One question that often comes up however is of the form: "How many people who chose reponse 1 to question A also chose response 1 to question B?"</P><P> </P><P>Here is an example which uses temporary tables and the Intersect function to answer the question: "What % of respondents who collect data on paper also store the data on paper?"<BR /><BR /></P><P> </P><LI-CODE lang="markup">ManualCollectAndStore =
VAR ManualCollect =
CALCULATETABLE(
DISTINCT('Raw Data'[Respondent]),
FILTER('Raw Data',
'Raw Data'[QuestionRoot] = "CollectMethod" && 'Raw Data'[RowIndex] = 1 && 'Raw Data'[Value] > 0
)
)
VAR ManualStore =
CALCULATETABLE(
DISTINCT('Raw Data'[Respondent]),
FILTER('Raw Data',
'Raw Data'[QuestionRoot] = "StorageMethod" && 'Raw Data'[RowIndex] = 1 && 'Raw Data'[Value] > 0
)
)
RETURN
COUNTROWS(INTERSECT(ManualCollect, ManualStore)) /
COUNTROWS(ManualCollect)</LI-CODE><P> </P><P>(Answer is 43.6% in case you are interested)</P><P> </P><P>Question 1: Make sense? Any suggestions?</P><P> </P><P>Question 2: Is it possible in Power BI to generalize this to the entire dataset? In other words, allow the user to select any two question/response pairs and see the resulting intersect value?<BR /><BR />Thanks for any guidance!</P><P> </P><P> </P>Wed, 03 Mar 2021 20:53:32 GMThttps://community.powerbi.com/t5/Desktop/Using-Intersect-function/m-p/1701820#M675232sfmike992021-03-03T20:53:32ZRe: Using Intersect function
https://community.powerbi.com/t5/Desktop/Using-Intersect-function/m-p/1701960#M675268
<P>Hi,</P>
<P>With disconnected tables this should be possible to solve. Share some data to work with.</P>Thu, 04 Mar 2021 00:12:50 GMThttps://community.powerbi.com/t5/Desktop/Using-Intersect-function/m-p/1701960#M675268Ashish_Mathur2021-03-04T00:12:50ZRe: Using Intersect function
https://community.powerbi.com/t5/Desktop/Using-Intersect-function/m-p/1704041#M675818
<P>Great - here's a sample with four questions and ten respondents. (source table is > 3m rows):</P><P><A href="https://1drv.ms/x/s!AvmRZllooYiOnyuQ3NNFa7laXSL9?e=giUbQ9" target="_blank">https://1drv.ms/x/s!AvmRZllooYiOnyuQ3NNFa7laXSL9?e=giUbQ9</A></P><P> </P><P>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?</P><P> </P><P>Thanks for looking at this.</P>Thu, 04 Mar 2021 20:48:40 GMThttps://community.powerbi.com/t5/Desktop/Using-Intersect-function/m-p/1704041#M675818sfmike992021-03-04T20:48:40ZRe: Using Intersect function
https://community.powerbi.com/t5/Desktop/Using-Intersect-function/m-p/1704185#M675851
<P>Hi,</P>
<P>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?</P>Thu, 04 Mar 2021 23:38:44 GMThttps://community.powerbi.com/t5/Desktop/Using-Intersect-function/m-p/1704185#M675851Ashish_Mathur2021-03-04T23:38:44ZRe: Using Intersect function
https://community.powerbi.com/t5/Desktop/Using-Intersect-function/m-p/1705897#M676258
<P>Ah sorry - I see where that is confusing. </P><P> </P><P>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. </P><P> </P><P>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. </P>Fri, 05 Mar 2021 15:20:17 GMThttps://community.powerbi.com/t5/Desktop/Using-Intersect-function/m-p/1705897#M676258sfmike992021-03-05T15:20:17ZRe: Using Intersect function
https://community.powerbi.com/t5/Desktop/Using-Intersect-function/m-p/1707575#M676699
<P>Armed with the suggestion for disconnected tables and some head scratching time, I think I figured this out.</P><P> </P><P>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:<BR /><BR /></P><UL><LI>Numerator: everyone who chose selected question/response options for BOTH questions</LI><LI>Denominator: everyone who chose selected question/response for A and ANY response for B question</LI></UL><P>Here's the DAX: </P><LI-CODE lang="markup">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))</LI-CODE><P> </P><P>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.</P><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="sfmike99_1-1615158234333.png" style="width: 400px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/468865i7BDE9F436E3B3E3A/image-size/medium?v=v2&px=400" role="button" title="sfmike99_1-1615158234333.png" alt="sfmike99_1-1615158234333.png" /></span></P><P><BR />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.<BR /><BR />Make sense? Any suggestions?</P>Sun, 07 Mar 2021 23:05:47 GMThttps://community.powerbi.com/t5/Desktop/Using-Intersect-function/m-p/1707575#M676699sfmike992021-03-07T23:05:47ZRe: Using Intersect function
https://community.powerbi.com/t5/Desktop/Using-Intersect-function/m-p/1707998#M676794
<P>Hi <LI-USER uid="91050"></LI-USER> ,</P>
<P> </P>
<P>Could you pls upload your .pbix file if there' s no confidential information?It would be beneficial for test.</P>
<P> </P>
<P>Best Regards,<BR />Kelly</P>
<P><STRONG>Did I answer your question? Mark my post as a solution!</STRONG></P>
<P> </P>Mon, 08 Mar 2021 05:23:00 GMThttps://community.powerbi.com/t5/Desktop/Using-Intersect-function/m-p/1707998#M676794v-kelly-msft2021-03-08T05:23:00ZRe: Using Intersect function
https://community.powerbi.com/t5/Desktop/Using-Intersect-function/m-p/1709785#M677257
<P>Thanks <LI-USER uid="194507"></LI-USER> 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. <BR /><BR />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.<BR /><BR /></P><P>LMK if anyone has any questions.</P>Mon, 08 Mar 2021 19:42:32 GMThttps://community.powerbi.com/t5/Desktop/Using-Intersect-function/m-p/1709785#M677257sfmike992021-03-08T19:42:32Z