Strange relationship ambiguity

[EmanuelTavares]

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:

And this is the relationship schema:

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:

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.

[mickey64]

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.

[EmanuelTavares]

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