Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
QuangMC
Frequent Visitor

How do you treat questionnaires in Power BI

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)

11Lorem
12Ipsum
13

null

14

Dolor

21

Lorem

22

Ipsum

23

null

24

Sit

31

null

32

Ispum

33

null

34

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

1LoremIpsumnullDolor
2LoremIpsumnullSit
3null

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

5 REPLIES 5
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
QuangMC
Frequent Visitor

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 ?

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
Community Support
Community Support

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:

vjunyantmsft_2-1707460122767.png

The final output is as below:

vjunyantmsft_3-1707460154041.png

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:

vjunyantmsft_0-1707460504427.png

The final output is as below:

vjunyantmsft_1-1707460526498.png



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
Super User
Super User

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.