USERELATIONSHIP not working as expected

Hello,

I have two tables (well, I have a lot more than two tables, but these are the two in question!  They are both related to tables other than each other, so it may be that the other relationships are part of the problem).

They are:

Payments - a fact table containing a row for every payment we have received from customers, and

Projects - a fact table containing a row for every project we are doing for customers.

There is no direct relationship between these tables, but they are indirectly linked via two paths.   

One path is:  Projects <-- TableA  <-- TableB --> Payments.

The other path is: Projects <-- TableA ---> TableC --> Payments

Both tables contain the columns PaymentCode (which describes the type of project, and should be unique within a customer's record as a customer cannot do the same type of project twice), and CustomerID.  I am trying to analyse how much customers spend on their projects, e.g. on average.

I have created a matching field on both tables called PaymentProjectLink, which concatenates the PaymentCode and the CustomerID, with a hyphen in the middle.  I had expected the PaymentProjectLink to be unique on the Projects table and not unique on the Payments table, as a customer typically makes multiple payments per project.  However, it turns out that the Projects table has a few duplicate project bookings which (for other reasons) I will not be removing, which means that there are duplicates in the ProjectCode field.  This is not a big problem for me, as the duplicates are for old projects that won't form part of my analysis.

I created a calculated column in Projects: