Reputation:
I have the following two tables in power bi that share a many to many relationship.
Table1
code | Name | QuestionID
11 | Test |123
12 | Test |123
Table 2
Code | Name | Answer | Compliant| QuestionID
11 |Test | Yes | 1 |123
12 |Test | Yes | 1 |123
458 |Test2 | NO | 0 |256
678 |Test3 | Yes | 0 |000
So as you can see in table 1 there is a QuestionId and code and in Table 2 their is also a matching QuestionID and code
I want to count only one answer for the questionID from Table2 where the question ID and code exists in Table 1
As you can see theirs two question answered for QuestionID 123 but i only want to take it as a count of one answer.(note their can be multiple answers for the same question)
I don't know how to filter the two tables.
This is what I tried
Measure =
CALCULATE (
COUNT ( 'Table 2'[Answer] ),
FILTER ( 'Table1', 'Table1'[Code] = 'Table 2'[code] )
&& 'Table 2'[Compliant] = 1
)
but this doesn't work cause on the FILTER when joining table 2 it breaks because it doesn't like the second table.
Upvotes: 1
Views: 8057
Reputation: 176
If i understood correctly your question translates to something like this: I want to count all distinct Question ID that exist on both tables.
Therefore try this measure
CALCULATE(
DISTINCTCOUNT(Table2[questionId]),
FILTER(
Table2,
RELATED(Table1[questionId])
)
)
A litle off topic but i advise againts using many to many relationship between tables even on power bi.
Upvotes: 0
Reputation: 40244
It doesn't expect a Table 2 column reference like that since columns generally have more than one row. This might work instead:
Measure =
CALCULATE (
COUNT ( 'Table 2'[Answer] ),
FILTER (
'Table 2',
'Table 2'[Code] IN VALUES ( 'Table1'[code] )
&& 'Table 2'[Compliant] = 1
)
) + 0
The relationship should handle the filtering of Table1 on Table2 though. So you should be able to write it more simply by applying Table1 as a filtering table like this:
Measure =
CALCULATE (
COUNT ( 'Table 2'[Answer] ),
'Table1',
FILTER ( 'Table 2', 'Table 2'[Compliant] = 1 )
) + 0
(In both cases, the + 0
is to return a zero instead of a blank. You can remove that if you prefer a blank instead.)
Upvotes: 1