Creating relationships between tables when the system considers that there is a circular dependency

[Anonymous]

Respect Colleagues, please help me solve the problem.

The model has tables: «Partners», «FactSales», «PlanSales», «Calendar».

1. The table «FactSales» must be linked to the table «Partners» by the field «PartnerID».
2. The table «PlanSales» must be linked to the table «Partners» by the field «PartnerID».
3. The table «FactSales» must be linked to the table «Calendar» by the field «Date», so that we can use Slicer for months.
4. The table «PlanSales» must be linked to the table «Calendar» by the field «Date», so that we can use Slicer for months.
5. The table «Partners» must be linked to the table «Calendar» by the field «Date», so that we can use Slicer for months.
6. Problem 1: if we link tables «FactSales» – «Calendar», «PlanSales» – «Calendar», «Partners» – «Calendar», then the system does not allow to link tables «FactSales» – «Partners», «PlanSales» – «Partners» (these connections are inactive). A message appears:

7. Due to this problem, when creating visualizations for «FactSales» and «PlanSales», values are not displayed:

8. Problem 2: if we link tables «FactSales» – «Partners», «PlanSales» – «Partners», «Partners» – «Calendar», then the system does not allow to link tables «FactSales» – «Calendar», «PlanSales» – «Calendar» (these connections are inactive).

9. Due to this problem, when creating visualizations for «FactSales» and «PlanSales», values for months are not displayed:

Question: how can we link the tables «FactSales» – «Partners», «PlanSales» – «Partners», «Partners» – «Calendar», «FactSales» – «Calendar», «PlanSales» – «Calendar», so that you can use Slicer for months and so that visualizations are built correctly?

Thank you for your help.

With respect, Anastasiya.

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

It is suggested to use a star schema like below instead of a schema with loops.

If you have a table pattern like below, with loops, then cross filtering can create an ambiguous set of relationships. For example, 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.

You may make the relationships between tables «FactSales» – «Partners», «PlanSales» – «Partners» inactive and other relationships active. If you want to calculate with the inactive relationship, you may use userelationship()  function to specify the inactive relationship to be used in a specific calculation as the one that exists between two tables.

Best Regards

Allan

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

[Anonymous]

Thanks a lot, Allan!

[Anonymous]

What is the date in the Partner table and what is its relationship to the Fact and Plan tables?

xcan you provide some sample data for each of the tables?

Thanks

[parry2k]

@Anonymous what is the purpose of linking partner with the calendar? You can make this inactive relationship and all other relationships active. 

Wherever you need to use this inactive relationship, use userrelationship function in the measure to make inactive relationship active.

[Anonymous]

Many Thanks, @parry2k!