CODEWITHSUNDEEP
powerbidaxpowerbi-desktopmeasure
user14241810
user14241810

Reputation:

how to create a measure that filters 2 tables

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

Answers (2)

pedro magalhaes
pedro magalhaes

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

Alexis Olson
Alexis Olson

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

Related Questions