Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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

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...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous ,

     

    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's avatar
HotChilli
Icon for Community Champion rankCommunity Champion
1 year ago

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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.