Cannot figure out how to correct relationship ambiguity

[ansa_naz]

Hi all

I have the following 4 tables:

Sites

Lifts

Customers

Sales

They are linked as below:

Lifts M --- 1 Sites

Sites M ---1 Customers

Customers 1 --- M Sales

Table structure is as follows:

Lifts:

LiftID    SiteID    LiftName

Sites:

SiteID   CustomerID     SiteName

Customers:

CustomerID     CustomerName

Sales:

SalesID    CustomerID    SiteID    LiftID    Amount    Date

How can I link the Sites and Lifts table to Sales, so that I can see Sales by LiftName and Sales by SiteName? If I try to create a relationship between Lifts and Sales using LiftID (the obvious choice initially for me) I get ambiguity errors in Power BI. Same with trying to create a relationship between Sales and SiteID

See below PBIX file with data:

https://1drv.ms/u/s!AuiIgc_S9J5JhbYbCO2jbeQPfpzmXw

Cheers for all help

[parry2k]

@ansa_naz I looked at your file and you can set direct relationship between your sales table and life and site table and that will give you the result

[ansa_naz]

Hi @parry2k I cannot remove the relationship between Site and Customer as it is needed for other visuals. Because of that, any relationship betwen Site/Sales and Lift/Sales cannot be activated:

Any ideas?

Cheers

[parry2k]

@ansa_naz I don;t think it will work based on your relationship, you have to set the relationship between you sales and other tables and you need to review other reports and make the changes. 

[ansa_naz · Accepted solution]

Hi @parry2k I managed to find an answer to this, it involved using the USERELATIONSHIP function to use an inactive relationship - didnt know the function could do that. Thanks for the reply