Need Help on Count using related tables

[Anonymous]

Hi All,

I have the following tables.

dimScale, dimOption and dimQuestion

Option	Scale	Concat	|	Question	Option	|	Question	QuestionGroup
Strongly Disagree	1	1-Strongly Disagree	|	Q1	Strongly Disagree	|	Q1	Group 1
Disagree	2	2-Disagree	|	Q1	Disagree	|	Q2	Group 2
Neutral	3	3-Neutral	|	Q1	Neutral	|	Q3	Group 2
Agree	4	4-Agree	|	Q1	Agree	|	Q4	Group 2
Strongly Agree	5	5-Strongly Agree	|	Q1	Strongly Agree	|	Q5	Group 3
ExtremelyDissatisfied	1	1-ExtremelyDissatisfied	|	Q2	ExtremelyDissatisfied	|	Q6	Group 4
Dissatisfied	2	2-Dissatisfied	|	Q2	Dissatisfied	|	Q7	Group 5
Satisfied	4	4-Satisfied	|	Q2	Neutral	|	Q8	Group 6
ExtremelySatisfied	5	5-ExtremelySatisfied	|	Q2	Satisfied	|	Q9	Group 6
Significantly Inferior	1	1-Significantly Inferior	|	Q2	ExtremelySatisfied	|	Q10	Group 7
Inferior	2	2-Inferior	|	Q3	ExtremelyDissatisfied	|
Same	3	3-Same	|	Q3	Dissatisfied	|
Superior	4	4-Superior	|	Q3	Neutral	|
Significantly Superior	5	5-Significantly Superior	|	Q3	Satisfied	|
Not at all effective	1	1-Not at all effective	|	Q3	ExtremelySatisfied	|
Not very effective	2	2-Not very effective	|
Effective	4	4-Effective	|
Extremely effective	5	5-Extremely effective	|

and factData

Country	SurveyType	Date	Question	Scale
Country 1	Type 1	1/1/2017	Q1	2
Country 1	Type 1	1/1/2017	Q1	4
Country 1	Type 1	1/1/2017	Q1	5
Country 1	Type 1	1/1/2017	Q1	4
Country 1	Type 1	1/1/2017	Q1	3
Country 1	Type 1	1/1/2017	Q2	3
Country 1	Type 1	1/1/2017	Q2	4
Country 1	Type 1	1/1/2017	Q2	5
Country 1	Type 1	1/1/2017	Q2	4
Country 1	Type 1	1/1/2017	Q2	4
Country 1	Type 1	1/1/2017	Q3	4
Country 1	Type 1	1/1/2017	Q3	4
Country 1	Type 1	1/1/2017	Q3	5
Country 1	Type 1	1/1/2017	Q3	4
Country 1	Type 1	1/1/2017	Q3	4

Now I want the results in the following format (for Group 2)

Response	Count
1-ExtremelyDissatisfied	0
2-Dissatisfied	0
4-Satisfied	7
5-ExtremelySatisfied	2

I have joins like

first 2 tables joined on 'Option'

2nd and 3rd on 'Question'

and again on 'Question' with 3rd and data table.

I hope this make sense. Any help would be appreciated.

Sorry for the not so good layout.

Kris

[Phil_Seamark · Accepted solution]

HI @Anonymous

One approach is to flatten your dimOption, dimScore and factData into 1 table and only join that to dimQuestion

I have created a sample PBIX file here using your data

https://1drv.ms/u/s!AtDlC2rep7a-oCFNYLAo0g761ph7

This includes the steps I have taken in the Query Editor to combine the three tables.

[Anonymous]

Thanks Phil. Works fine.

Appreciate your help.

Kris

[Anonymous]

Bump*