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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
EmanuelTavares
Advocate II
Advocate II

Strange relationship ambiguity

Hi,

 

I found some posts here talking about relationship ambiguity, but I'm still asking what the problem is with my model, and then I'm asking for some help from the community.

 

I have a table with stores and creation date and two tables with transactions:

Screenshot 2024-08-10 175256.png

 

And this is the relationship schema:

Screenshot 2024-08-10 175318.png

 

Now, I need a list of all stores created at a certain date interval and the count of their transactions.

 

To do this, I tried to establish a relationship between CALENDAR and STORE_DATE, but I received an error message:

Screenshot 2024-08-10 175444.png

 

I didn't understand why because the relationships from CALENDAR and STORE_DATE to TRANSACTIONS are in one single direction, and it looks like there is no way to TRANSACTIONS filter CALENDAR or STORE_DATE.

 

I'd like to understand this strange situation.

 

Someone can help, please?

 

Regards.

2 REPLIES 2
mickey64
Super User
Super User

For your reference.

 

I don't think there are any duplicate "Id"s in the "store_date" table, and each Id is linked to one date.
However, there are duplicate "Id"s in the "Transactions_A" table, and different dates may be linked to the same "Id", so I think it is not possible to set a relationship between the "calendar" table and the "store_date" table.

@mickey64 ,

 

I didn't understand the relation.

The data source is Bigquery, and I'm considering creating a view or something else.

I still think that it is a strange limitation. 

Asking to ChatGPT, it gave me this hypothesis:

"

The problem you are encountering with the error message about ambiguous paths does not necessarily stem from the direct influence of the transaction tables on the calendar or store_date tables, but from the existence of multiple paths that Power BI can choose to relate these tables due to the current settings.

 

Even though the relationships are configured to filter in a specific way (such as from calendar to transactions), Power BI still considers the existence of all possible paths when new relationships are proposed or queried, which can cause ambiguity.

"

 

In a relational database, we don't have this limitation.

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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