Forum Discussion
Group by ID
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
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,
3 Replies
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,
Thank you very much rsbin
Works as expected.
This measure may do the trick but depends on your actual data...
Questions Answered = CALCULATE( DISTINCTCOUNTNOBLANK(Questions[Question]), Questions[Question answered?] = "X" )
