Why is the error message that "You can't create a direct active relationship..."?

[Yiyi_1989]

Hello friends, 

Can you help me to understand why did I get such error message when trying to model my table relationships:

" You can't create a direct active relationship between Company Info and Query 2 because that would introduce ambiguity between table Query 2 and Query. To make this relationship active, deactive or delete one of the relationships between Query 2 and Query first"  

Query and Query 2 are two tables from two different PowerBi datasets that I connected with Azure Monitor Logs. I have created also a Date Table. My purpose is to connect the Date Table and the Company Info Table with both Query tables so that I could filter the data with date and company (industries, areas...). In details, I tried to connect field Date in Date Table with timestamp in both Query Tables, and field Azure Montor Logs in Company Info Table with OrganizationName in both Query Tables.

Can you give me some idea what went wrong and how can I fix this issue? 

Thanks a lot for your help in advance! 

[BA_Pete · Accepted solution]

Hi @Yiyi_1989 ,

It's because of your bi-directional relationships between the dimension tables and the fact tables.

Within your report, let's say you select DateTable[Date] = 2023-01-01.

This filters the Query table to only items on that date. Due to the bi-di relationship with the CompanyInfo table, CompanyInfo gets filtered to only companies that have a Query transaction on that date.

The filter on CompanyInfo now gets passed to the Query2 table, which Now shows only companies that feature within the filtered CompanyInfo table.

Finally, due to another bi-di relationship, Power BI tries to pass a filter back to DateTable to only show dates where a Qery2 transaction occurred for the filtered company.

As you can see, the date that you have initially selected and the date that your model tries to pass back to DateTable after all the relationships have activated may not be the same date. Hence, you have 'introduced ambiguity' into the model.

To fix, you need to properly structure your dimension tables so that they form ONE-to-MANY relationships with your fact tables in a single direction (dim > fact).

If you want to be able to filter both fact tables on any other common dimensions, then you need to set up new dimension tables for each of these and relate them correctly to both fact tables as described above.

Pete

[Yiyi_1989]

Thanksss Pete! I am new to Power BI and your answer really helped me a lot!