Issues with Filtering Across Indirect Relationships in Power BI between two tables

[ksahil16]

I'm having trouble with filtering across multiple tables where there's an indirect relationship, and I'd appreciate some guidance on how to fix this. Here's my setup:

Food Entities - Table showing the metions of food and its associated published on date

Id	Food	Publishedon
1	Apple	25-01-2024
2	Pear	26-02-2024
3	Apple	26-02-2024
4	Banana	26-02-2024
5	Banana	28-03-2024

Intermediate table - Table to create a connection between Food entities table and Relation Association table 

Intermediate table

Apple

Pear

Banana

Relation_Association - Table showing when a Food and contaminant and mentioned together and its associated PublishedOn date

Id	Food	Contaminant	PublishedOn
1	Apple	Cont A	25-01-2024
2	Apple	Cont B	26-02-2024
3	Apple	Cont A	26-02-2024
4	Banana	Cont C	28-02-2024
5	Pear	Cont B	01-03-2024
6	Banana	Cont A	02-03-2024
7	Banana	Cont C	02-03-2024

NOTE: The Food_Entities table and the Relation_Association table are connected to each other using a bidirectional relationshipo through the Intermediate table.

My main goal is to create two table visuals- First one where I display the Food and the its counts based on the "PublishedOn" filter from Food Entities table, and the second table which also gives me the Count of the Contaminant column from the Relation_Association table. 

So for example if I apply the filter for the PublishedOn column and select the date 26-02-2024 and everythign after that. My first table should display:

Visual Table 1: 

Food	Count
Apple	1
Pear	1
Banana	2

Visual Table 2:

Contaminant	Count_contaminant
Cont A	2
Cont B	2
Cont C	2

And the Count_contaminant column values should also change on the filter PublishedOn from the Food Entities table.

What I tried:
Measure I created for Count_contaminant: 

Count_contaminant = CALCULATE(

    COUNT('Relation_Association'[food]),

    'Food_Entities'[published_on]

)

when I apply a filter on the published_on column in the Food_Entities table, it doesn't seem to affect the result of this measure. The data in Relation_Association remains unaffected by the date filter.

How can I modify the measure or the relationships to ensure that the filter applied on Food_Entities[published_on] affects the count of food entities in Relation_Association?

Do I need to change the relationship settings or write a more complex measure to ensure the filter propagates properly through the intermediate table?

Any help would be appreciated.

[Anonymous · Accepted solution]

Hi @ksahil16 ,

Please create a new calendar table:

Calendar = 
CALENDAR (
    MIN (
        MIN ( 'Food_Entities'[Publishedon] ),
        MIN ( 'Relation_Association'[PublishedOn] )
    ),
    MAX (
        MAX ( 'Food_Entities'[Publishedon] ),
        MAX ( 'Relation_Association'[PublishedOn] )
    )
)

Two-side relationships should be avoided in the model as much as possible, and if needed please turn them on in the code:

Count_contaminant = 
CALCULATE (
    COUNT ( 'Relation_Association'[food] ),
    CROSSFILTER ( 'Intermediate table'[Intermediate table], 'Food_Entities'[Food], BOTH )
)

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[HotChilli]

I don't think that answers my first question. Both tables have Food in them and they both have dates but what do they have to do with each other?

[HotChilli]

In a business sense what does table Food Entities have to do with table Relation_Association?

Is it just the date which you want to filter on? In which case, can you remove the intermediate table, create a date dimension which links 1:n to both tables and use date from the date dimension in the filter?

[ksahil16]

Thank you for the reply, it is not just the date that I have to filter on. The Relation_Association table also has the food column, so the logic that we imply here is that "Visual Table 2:" is also also somehow filtered based on the food column.

For Example as a User I want to know what were the Contaminants associated with Apple during a period (publishedOn) column. So if I click on Apple in the Visual table 1, it should give me the contaminants associated with apple in Visual table 2: and if we filter the date again the Count in the Count_Contaminant column should change accordingly.