Cross-tabulation based on multiple-choice questions

[mnemolu]

We recently conducted a survey featuring multiple-choice questions, allowing participants to select any number of answers for each question.

Our objective is to perform a cross-tabulation based on the collected data in a visually straightforward format. Despite attempting various approaches, achieving this seemingly simple visualization has proven challenging.

The following illustration provides a simplified depiction of our desired outcome. Any assistance in this matter would be greatly appreciated.

Sample data:

user_id	question	selected
1	favorite sports	basketball
1	favorite sports	football
2	favorite sports	basketball
3	favorite sports	basketball
3	favorite sports	football
3	favorite sports	swimming
1	favorite transportation means	subway
1	favorite transportation means	airplane
2	favorite transportation means	subway
3	favorite transportation means	subway
3	favorite transportation means	train
3	favorite transportation means	airplane
1	favorite phone brands	Apple
1	favorite phone brands	Huawei
2	favorite phone brands	Samsung
3	favorite phone brands	Huawei
3	favorite phone brands	Apple
3	favorite phone brands	Samsung

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.

[Daniel29195 · Accepted solution]

@mnemolu 

output : 

example 1 :

explanation of how it works : 

the nb of users displayed =  nb of users that likes apple  and plays basketball .

example 2 : 

explanation of how it works : 

the nb of users displayed =  nb of users that likes apple and samsung  and plays basketball .

Measure = 

var answers =  VALUES('Table2'[selected])
var total_answers =  COUNTROWS(answers)

var users =  VALUES(Table2[userid])


var add_col =
ADDCOLUMNS(
    users,
    "@X" ,  CALCULATE(DISTINCTCOUNT(Table2[selected]))
)

var res = 
FILTER(
    add_col,
    [@X]  = total_answers
)

var calc = 
CALCULATE(
    DISTINCTCOUNT(Table2[userid]),
    res
)


RETURN
calc

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅!

It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[mnemolu]

Thank you very much, I just realized this measure works for cross-tabulation too.

[mnemolu]

Thank you so much for your assistance; I've successfully calculated the number of users using the measure you provided. However, I'm still grappling with creating the cross-tabulation visual.

If the questions were single-choice, it would have been very easy. These multiple-choice questions seem to make the cross-tabulation a lot more challenging.