Survey/Questionnaire Data Model Complex Filtering

[Anonymous]

Hi all,

we get several different structured surveys. We load them in one table, where one answer to a question is the granularity. This makes it very flexible in loading new surveys or questions, because this only result in more rows and not columns.

Our Strucuture is similar to  https://community.powerbi.com/t5/Desktop/Complex-filtering-problem/m-p/683274 and I really like the slicer Idea. I also know the "Many to Many Revolution".

In our Case the user wants to filter on more than one Question-Answer e.g.

"Show me the Average of Product Satisfaction? of all Customers that answered Gender? with Male and Overall Satisfaction? with 1."

At the moment we are thinking of pivoting all different surveys (e.g. 20 different Surveys) that results in a lot of smaller Tables and also a lot of work in creation (we will relate them to some common dimensions) and maintenance.

Do you know any smart solution to this Problem?

Thank you and Best Regards

[v-yuta-msft]

@Anonymous ,

Could you please share some sample data and clarify more details about your requirement?

Regards,

Jimmy Tao

[Anonymous]

Hi,

the table looks like the following:

survey_type	survey_id	row_id	customer_id	question	answer
S1	1	1	123	Satisfaction?	1
S1	1	2	123	Gender?	Male
S1	1	3	123	Age?	32
S1	2	4	245	Satisfaction?	3
S1	2	5	245	Gender?	Female
S1	2	6	245	Age?	56
S1	3	7	567	Satisfaction?	1
S1	3	8	567	Gender?	Male
S1	3	9	567	Age?	45
S2	4	10	123	Satisfaction?	3
S2	4	11	123	Sports?	Soccer
S2	4	12	123	Sports?	Football

Now the want to have a Visual thats shows the Avergae Satsifaction of all Customers. And probably the Count of Customers that are Male/Female. If they click Male, the Average Satisfaction should display only Male Customers. But also all other Visual e.g.: Show Avergae Age of all Male Customer, Show Average Satisfaction of another Question 4, and so on. There can be more than 3 Questions in one Questionnaire.

Best Regards