Group by ID

[qwertzuiop]

Hello dear Power BI Community

I have the following problem to solve - let's assume this situation:

Participants in a questionnaire give various answers to diffrent questions.

They can answer these with good, bad or not at all.

To make this more clear and explain it in more detail:

For example, participant P1 answered question A with "good", question B with "bad" and gave no answer to C.

In addition, a reason why is required when rating a question as "bad" (see. lorem ipsum)

Note: An answered question as bad can have several reasons why (see P2 question C)

Participant-ID	Question	Question answered?	Good	Bad	Reason why if bad
P1	A	X	X
P1	B	X		X	lorem ipsum_1
P1	C
P2	A	X	X
P2	B	X	X
P2	C	X		X	lorem ipsum_2
P2	C	X		X	lorem ipsum_3
P3	A
P3	B	X	X
P3	C	X	X

The goal is to create a grouped table like this below:

A cross table showing the number of answered questions (good or bad) in relation to the number of participants.

To make it more clear for example:

2 participant (P1 & P3) answered 2 questions

1 participant (P2) answered 3 questions -> Here is the main challenge:  The result should be 3 and not 4 (because the participant answered question C twice in this data structure, but in fact it is only because of the two reasons why).

Num_of_Participant	Question answered
2	2
1	3

Any ideas how to solve this problem?

Thank you very much for your contribution.

Cheers

qwertzuiop

[KNP]

This measure may do the trick but depends on your actual data...

Questions Answered = 
    CALCULATE(
        DISTINCTCOUNTNOBLANK(Questions[Question]), 
        Questions[Question answered?] = "X"
    )

[rsbin · Accepted solution]

@qwertzuiop ,

Please see attached pbix.

Solution involves creating one Calculated Column.

Then creating your Table (or other) visual.

Hope this works for your larger dataset.  Let me know if you have any questions.

Regards,

[qwertzuiop]

Thank you very much @rsbin 

Works as expected.