Forum Discussion
Filter AND Multiple Column from 2 existing tables
Been trying to do a calculate count from 2 separate tables connected in Many to one (Both) Relationship.
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
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.
4 Replies
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"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.
