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:
ProjectTotal = CALCULATE([SumPaymentAmount],USERELATIONSHIP(Projects[PaymentProjectLink],Payments[PaymentProjectLink]))
(The measure [SumPaymentAmount] just sums up the amount from the Payments table - it normally works fine)
However, the ProjectTotal calculated column is not bringing me the totals I expected. It is pulling through some payment data for some customers, but this seems to be based on another relationship. The relationship it's using is that which connects TableX --> TableC.
I suspect all the relationships and paths are the problem... any advice? Many thanks in advance.
P.S. My organisation does not permit me to upload a Power BI file to this forum, so I'm afraid I can't do that.
