Database relationships and filtering

[AndyU]

Hello,

I have two tables with the that are linked in a relationship. Table A contains all the information I wish to present, and table B is just to check certain criteria for the same rows and to filter out unwanted ones from the data presented based on Table A. However, it seems like PowerBI is filtering out any rows that are non-existant in table B, not only the ones I have asked it to filter based on certain criteria. Example:

Table A:

Relationship Column	Data to be presented
1	33
2	45
3	634
4	623
5	456
6	458
7	354

Table B:

Relationship Column	Filter Criteria
1	Red
2	Blue
3	Green
4	Red
5	Yellow

In this example, I wish to filter away Blue and Green, so that only data from Red and Yellow is presented. In addition, I would also like to present the data in rows 6 and 7 in table A, even though it is not present in table B, as I am only looking to filter away any Blues and Greens that are present in table B. Right now PowerBI seems to be filtering away 6 and 7 as well since it can't find them in table B.

Any help would be appreciated!

[v-danhe-msft]

Hi @AndyU,

Based on my test, you could refer to below formula:

Measure = CALCULATE(MAX('TableB'[Filter Criteria]),FILTER('TableB','TableB'[Filter Criteria]<>"Blue"&&'TableB'[Filter Criteria]<>"Green"))

And choose the 'Show items with no data':

You could also download the pbix file to have a view.

Regards,

Daniel He

[AndyU]

Hi Daniel,

Thanks for your suggestion. I tried playing around with it but I couldnt get it to do what I wanted. After some testing it seems like the issue is that in the real database (not the example I gave), table B has duplicates in the relationship column, which prevents a one to one cardinality. Once I deleted the duplicates, it seems to be working fine and is including all those entries in table A that do not exist in table B. However, this is not an optimal solution was I don't want to keep manually going through the duplicates and then deleting them or renaming. 

Is there a way to make this work with duplicates?