Ambiguous relationship and how to adjust?

[ARomain57]

I have some tables in Powerbi and am getting am ambiguious relationship error. I understand why I have this error, I just honestly have no idea how to fix this. Asking for some pointers.

At the moment, my model works and the relationhsips are below. 

PatientInfo is my main table. I want this to be the center of the model. The only field that should link out of this table to any other is CaseID

ApproachInfo on the bottom left is linked to PatientInfo by CaseID. However, the direction is currently only PatientInfo --> ApproachInfo, and I would like it to be both directions. The cardinality can also be 1:1, but is not at the moment due to the ambigious error.....

ApproachInfo is connected to Contact table (top left) by ContactID. However, I also have an issue in which I have 2 different columns with 2 different contactIDs. I currently have an active and inactive relationship between these tables.

AuthorizationForm (upper middle) is connected to PatientInfo by CaseID and Contact by ContactID

StaffTracking (bottom right) is connected to PatientInfo by CaseID and Contact by ContactID

I want my ApproachInfo <--> PatientInfo but I am getting am ambigious error, which is expected. 

My quesiton is....how do I fix this? What would I need to do to make this model better? If I need a bridge table (not sure if I do or not), what exactly would I need?

[Anonymous]

Hi @ARomain57 ,

Did MNedix reply solve your problem? If so, please mark it as the correct solution, and point out if the problem persists.

Best regards,

Adamk Kong

[MNedix]

Heya,

Not having too much info, I would simply merge ApproachInfo and PatientInfo in a new query and expand/retain the columns I am interested in. To do that, go to Transform Data (Power Query) -> Home tab -> Merge Queries -> Merge Queries as New. Use PatientInfo as first table, ApproachInfo as 2nd, merge them by the CaseID column and do a Left Outer join; I don't know if Left Outer is the best for your case, you can experiment with other joins. Then, in the new query, expand the newly added table and retain the columns you are interested in.

Second, you may want to fix the 2 ID columns in Contact, perhaps via a 3rd calculated column. This will probable bring more clarity in Contact IDs.

Hope it helps.

[ARomain57]

Thank you! Will try that