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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
HitcH
Helper II
Helper II

Third table relationship

Hello powerBI people!

This is my problem

Table 1: Month, Product, Sales

Table 2: Month, Product, Target

I want to create a realtionship with "Month", but the field is not unique and so I can't do it.

Then I created Table 3 with only "Month" dimension.

I create the relationship Table 1 to Table 3 and that is fine, but when I create the relationship between Table 2 and Table 3 I can't make it active, as I get this error message: "you can't create a direct active relationship because an active set of indirect relationship already exists", saying that it would create an ambiguity between Table 1 and Table 2.

Thanks in advance for any support

1 ACCEPTED SOLUTION
timahenning
Frequent Visitor

Here is my guess to your problem. You may have an existing relationship between table1 and table2. When you are trying to create an active relationship between your month dimension and table2, you get an ambiguity error.  There would be two possible paths for the engine to get to table2.

timahenning_0-1677453419091.png

Have you thought about this design?

timahenning_1-1677454304243.png

It will allow you to filter and group by these dimensions:

timahenning_2-1677454491462.png

 



View solution in original post

6 REPLIES 6
timahenning
Frequent Visitor

Here is my guess to your problem. You may have an existing relationship between table1 and table2. When you are trying to create an active relationship between your month dimension and table2, you get an ambiguity error.  There would be two possible paths for the engine to get to table2.

timahenning_0-1677453419091.png

Have you thought about this design?

timahenning_1-1677454304243.png

It will allow you to filter and group by these dimensions:

timahenning_2-1677454491462.png

 



timahenning
Frequent Visitor

Do any joins exist between Table1 and Table2?

Not direct, but I think there is some indirect relation with the field "Product", as they both are linked to the table with product details.

I think that is the problem, but I cannot break that relationship..

Hi,

Just as you created a Month dimension table, you should also create a Product dimension table.  Remove any relationships between Table1 and Table2.


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

Thank you Ashis!
One additional question, now I could handle it, but I still have some problem on the Month relationship:

HitcH_0-1677517687906.png

You can see the two tables connected to each other via the "match" table in the middle. Still if I create a table with YearMonth1 and YearMonth2 together I get an error (while I can create YearMonth1 with YearMonthMatch or YearMonth2 with YearMonthMatch singularily). What am I still missing? 😞

 

Thanks again!

Hi,

In each of the Fact tables you should create a Date column from the Yearmonth column.  Create a Calendar Table.  Build a relationship from the Date column of hte 2 Fact Tables to the Date column of the Calendar Table.  In the Calendar Table, create columns of Year, Month name and Month number.  Sort the Month name column by the Month number column.  To any visual/slicer, drag any Date related column from the Calendar Table.


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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors