Power BI Measure to countrows of related values several related tables deep

user_910737 0
Viewed 149

I need to create a measure for a card that will count the total number of Question Groups that exist for each person using the tables below.

I've tried the following but it's returning the result 10, instead of the expected result which should be 6. (George = 2, Susan = 1, tom = 1, bill=1, sally =1, mark =0, jason=0)

Measure = COUNTROWS(NATURALLEFTOUTERJOIN(NATURALLEFTOUTERJOIN(People,Questions),'Question Groups'))

What am I doing wrong?

Table: People

PeopleID Name
1 George
2 Susan
3 Tom
4 Bill
5 Sally
6 Mark
7 Jason

Table: relPeopleQuestions

PeopleID QuestionID
1 1
1 2
1 3
2 4
2 5
3 6
4 7
5 8

Table: Questions

Question ID Question name Questiong Group ID
1 How are you? 1
2 Favorite Color? 2
3 Favorite Movie? 2
4 Sister's Name 3
5 Brother's Name 3
6 What is your birthdate? 1
7 What City do you live in? 1
8 Favorite game? 2

Table: Question Groups

Question Group ID Question Group Name
1 Assorted
2 Favorites
3 Relatives

A working example file can be obtained here.

dax powerbi measure
1 Answers

A distinct count on the Question Group ID from the Questions table would seem to be sufficient, e.g.

MyMeasure =
VAR MyTable =
    SUMMARIZE (
        People,
        People[Name],
        "Count", DISTINCTCOUNT ( Questions[Question Group ID] )
    )
RETURN
    SUMX ( MyTable, [Count] )
user_170077040
Related