3 tables and 2 date fields that doesn't have direct relationship

[Will90]

I have 3 tables lets says PRODUCED, CENTRE, PLAN.

Table PRODUCED has

ID*, ORDER, DESCRIPTION, WEEK

Table CENTRE has

ID*, LOCATION

Table PLAN has

ID*, DESCRIPTION, WEEK

Tables relationship are connected ORDERS to CENTRE via ID & CENTRE to SUPPLY via ID

I want to connect WEEK column together so i can see what has been produced against plan? Both week is many to many so I tried to create another table (WEEK) like CENTRE but then I get an error:

> You can't create a direct active relationship between ORDERS and PLAN because that would introduce ambigutiyu between tables Week and Centre.

What can I do to resolve this problem so I can only have 1 drop down field for Week to see values from both tables that are not directly linked with a relationship?

[Ashish_Mathur]

Hi,

You should ideally create a Calendar Table with a week column.  Then build a relationship from the week column of the two tables to the week column of the Calendar Table.  In your visual/filter/slicer, drag Week from the Calendar Table.

[Will90]

Hi,

This is what I tried to do, build a calendar table like CENTRE but got this error. 

> You can't create a direct active relationship between ORDERS and PLAN because that would introduce ambigutiyu between tables Week and Centre.

How can i attach files so I can share my file?

[Ashish_Mathur]

Hi,

Upload your files to Google Drive and share the download link here.

[Will90 · Accepted solution]

Hi,

What I had to do was build a new key table and then linked my weeks table to that one (total of 3 new tables) - I think I've got the problem now resolved. 

[Will90]

How can I attached my pbix file here to show you?

[v-yulgu-msft]

Hi @Will90,

Based on my understanding, since there existing relationship betwen PRODUCED and PLAN via ID column, creating an extra relationship via WEEK might cause ambigutiyu for determining the mapping rules between records in two table. 

Please provide some sample data and desired output to make your requirement more clear.

Regards,

Yuliana Gu