Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Will90
Frequent Visitor

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

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?

1 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. 

 

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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?

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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
Frequent Visitor

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

v-yulgu-msft
Employee
Employee

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.