Filter one table from different tables

[ksivaganesh99]

Spoiler

Hi all,

I have four related tables to be modelled in the following manner. But not able to do as it is creating circular filtering across the tables. What is the best way I can handle this?

Any help is highly appreciated.

[v-alq-msft]

Hi, @ksivaganesh99 

I'd like to suggest you use a star schema instead of a table pattern with loops. Cross filtering both directions works well for a pattern of table relationships as below.

If you have a table pattern like below, with loops, then cross filtering can create an ambiguous set of relationships. If you sum up a field from TableX and then choose to filter by a field on TableY, then it’s not clear how the filter should travel, through the top table or the bottom table.

For further information, please refer Create and manage relationships in Power BI Desktop 

Best Regards

Allan

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

[amitchandak]

@ksivaganesh99 , can not say without knowing data. But Table 2- Table 1 and Table2 - Table3 can be inactive join, use as per need.

[edhans]

You cannot create filters like that. It will create ambiguity, as you've found. You need to try and develop your model as a Star Schema.

Within Power Query you should build the tables you need then only load what you need into DAX, then relate those with a FACT table surrounded by DIM tables. SQLBI has written an entire book on this and has a course on it.

I literally spend hours whiteboarding how the model will look and how to pull in the right data. I NEVER just grab 15 data sources, load 15 tables, then start connecting them in the Power BI modeling tab. I spend a TON of time in Power Query shaping the data to get it down to what I really need in DAX. Then my measures become pretty easy.