Forum Discussion
Using Intersect function
- 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?
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?
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!
- sfmike995 years agoAdvocate II
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.