Filter AND Multiple Column from 2 existing tables

[v_mark]

Been trying to do a calculate count from 2 separate tables connected in Many to one (Both) Relationship.

CALCULATE(COUNT('Problem'[ProblemID]),

'Problem'[Status] IN { "Resolved", "Closed" } &&

'Task'[Title] IN { "Team A", "Team B", "Team C", "Team D" } &&

'Task'[Status] = "Closed" )

Unfortunately getting an error Like the one below


Looks like if I will remove the && the outer filter is not being honored. 
Would like to pass all the conditions as being met. Might need to re-tweak how I write this.  Please help

[v-robertq-msft · Accepted solution]

Hi, @v_mark 

According to the error description, it says that you can’t use the columns from the different tables to declare as a true/false expression. Therefore, I think that there are two ways to solve this problem.

One is to use the Related() function to quote the column from other tables and change the measure like this:

Measure=

CALCULATE(COUNT('Problem'[ProblemID]),

Filter(‘Problem’,

'Problem'[Status] IN { "Resolved", "Closed" } &&

Related('Task'[Title]) IN { "Team A", "Team B", "Team C", "Team D" } &&

Related('Task'[Status]) = "Closed" ))

More info about the Related() function in DAX

Or you can go to the power query editor to merge the two tables using the related key of their relationship, like this:

More info about merging Two Tables In Power BI

If you still have a problem, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[NilR]

are they related? any key that you can join both tables? something like below

Measure =... related(Task'[Title]) IN { "Team A", "Team B", "Team C", "Team D" } &&

related('Task'[Status]) = "Closed"

[v_mark]

Just to confirm when you say join both tables. are you talking about doing a merge?
Which I can do inside PQ 
OR Is there a way to avoid doing merge ?

[NilR]

No this is DAX, and not merge in PQ. if your tables have common fields you can use related. Just update your current dax and add related.