How do you treat questionnaires in Power BI

[QuangMC]

Dear all,

Let's say you have a questionnaire and need to make multiple viuals with different operations. 

I can think of 2 ways to structure my "Answers" table :

1 - Lots of rows, minimal columns

This structure would mean to have a minimal amount of columns, but have lots of rows.

Client (Key, linked with a "Clients" table")	Question  (Key, linked with a "Questions" table")	Answer  (Not a key)
1	1	Lorem
1	2	Ipsum
1	3	null
1	4	Dolor
2	1	Lorem
2	2	Ipsum
2	3	null
2	4	Sit
3	1	null
3	2	Ispum
3	3	null
3	4	Amet

2 - Less rows, MORE columns

In this structure, the idea would be to have 1 column for each and every question, which would limit the number of rows.

Client (Key, linked with a "Clients" table")	Question 1	Question 2	Question 3	Question 4
1	Lorem	Ipsum	null	Dolor
2	Lorem	Ipsum	null	Sit
3	null	Ipsum	null	Amet

While I would personnally go for the first option, the operations that are asked in the report are a bit tricky. For instance, How would you Count the distinct number of clients that have answered "Lorem" in question 1, AND "Ispum" in question 2 ? 

I have made measures for both scenarios, and find that with more complex demands (adding more conditions for instance), the easier way would be with the second one.

However, this becomes harder to manage when you get over a hundred questions, and thousands of clients.

So in the end, how would YOU treat such things ? Which of those ways would you use, and what would your measures look like ? Is there another way I am not seeing ? 

I can give more information if needed.

Regards

[Ashish_Mathur]

Hi,

The first table is the preferred one.  You may try this measure

Client count = distinctcount(Data[Client])

Measure1 = calculate([Client count],Data[Question]=1&&Data[Answer]="Lorem")+calculate([Client count],Data[Question]=2&&Data[Answer]="Ipsum")

Hope this helps.

[QuangMC]

Thank you both @amustafa and @v-junyant-msft.

Both answers look good and point towards having the first structure.

However, is it possible to implement the Power Query part within measures ? While I do have more basic calculations where @amustafa 's measures are perfect, I have more questions that need the Power Query option. It is interesting, but hard to sustain when you have tens of similar questions, which leads to many columns, and many operations in the query.

Any ideas ?

[v-junyant-msft]

Hi @QuangMC ,

Sorry for the late reply. power Query can do a lot more than creating a measure using DAX. If you want to cover all Power Query functionality with a measure it may not be realistic.

Best Regards,
Dino Tao

[v-junyant-msft]

Hi @QuangMC ,

If you have access to Power Query, then the calculations for the first table won't be too complicated. For example, you mentioned "Count the distinct number of clients that have answered "Lorem" in question 1, AND "Ispum" in question 2 ?"
We can use this simple method in Power Query to calculate this question:

The final output is as below:

Only further processing is needed to remove the null values, and it is also very clear to see how each question was answered.

Or you can use Group By:

The final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amustafa]

Based on the structure of your first table layout (recommended), here are some key DAX measures that you might consider creating:

1. Count of Responses: To count the number of responses for each question.
DAX
Count of Responses = COUNTROWS('Survey')

This will count the number of rows in the Survey table, which is equivalent to the number of responses.

2. Count of Completed Responses: To count the number of non-null responses.
DAX
Count of Completed Responses = CALCULATE(COUNTROWS('Survey'), NOT(ISBLANK('Survey'[Answer])))

This measure counts the rows where the Answer column is not blank.

3. Response Rate: To calculate the response rate for each question.
DAX
Response Rate = DIVIDE([Count of Completed Responses], [Count of Responses])

This measure will give you the proportion of non-null responses to the total responses for each question.

4. Distinct Clients: To count the number of distinct clients who participated in the survey.
DAX
Distinct Clients = DISTINCTCOUNT('Survey'[Client])

This measure will count the unique number of clients in the Survey table.

5. Average Responses per Client: To calculate the average number of responses per client.
DAX
Average Responses per Client = DIVIDE([Count of Responses], [Distinct Clients])

This measure calculates the average number of responses submitted by each client.

6. Most Common Answer: To identify the most common answer for each question.
DAX
Most Common Answer =
CALCULATE(
MODE.SINGLE('Survey'[Answer]),
FILTER('Survey', NOT(ISBLANK('Survey'[Answer])))
)

This measure finds the most frequently given answer for each question, excluding null responses.

7. Percentage of Null Responses: To calculate the percentage of null or missing responses for each question.
DAX
Percentage of Null Responses =
CALCULATE(
DIVIDE(COUNTBLANK('Survey'[Answer]), COUNTROWS('Survey')),
ALL('Survey'[Question])
)

This measure calculates the proportion of null responses out of the total responses for each question.

These measures provide a comprehensive view of your survey data, helping you understand response patterns, engagement levels, and common trends in the answers. Remember to adapt these measures to your specific analysis needs and ensure they align with your data structure and business context.