Distinct count on more than one column for unpivoted table

[Lumc88]

 I have unpivoted a table of survey data becuase the respondents could select more than one of several options for one of the questions.  I am now trying to do a distinct count, however, it only counts distinct Staff IDs, I need a distinct count based on the 'Staff ID' and 'Question 2' i.e. 2 staff members responded 'Yes' 

Staff ID	Question 1	Question 2
1	Test 1	Yes
1	Test 2	Yes
1	Test 3	Yes
2	Test 4	Yes
2	Test 5	Yes
2	Test 6	Yes
3	Test 7	No
3	Test 8	No
3	Test 9	No

Would be very grateful if someone could advise how best to do this!

Thank you!

[Daniel29195 · Accepted solution]

@Lumc88 

calculate( distinctcount( tbl_name[ staff ID ] , tbl_name[Question 2] = "Yes" ) 

let me know if this helps .

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! 🤠

[vanessafvg]

then do a distinct value on  a concatenation of the fields  so concatenate the fields into one and do a distinct count them.

[Daniel29195 · Accepted solution]

@Lumc88 

calculate( distinctcount( tbl_name[ staff ID ] , tbl_name[Question 2] = "Yes" ) 

let me know if this helps .

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! 🤠

[Lumc88]

Thanks for the suggestions.

I couldn't get either to work for me, however, I figured out a solution using a quick measure as below;

Returners =

CALCULATE(

    DISTINCTCOUNT('Leavers New'[StaffID]),

    'Leavers New'[Question2]

        IN { "Yes" }

)

[Daniel29195 · Accepted solution]

@Lumc88 

the code written by quick measure  is equal to : calculate( distinctcount( tbl_name[ staff ID ] , tbl_name[Question 2] = "Yes" ) 

did you change the tbl_name to your table name ?