Forum Discussion
Relationship direction problem
Hi,
I'm trying to get the number of credits (sum of field brochure.creditvalue) for each account (field crm_account.accountname) grouped by month/year (field brochureorders.created).
I keep getting the wrong values. I changed the relationship between brochureorderdetails and brochures to both directions but the sum of credits is still not correct
I also added a measure to count then number of brochures (nrOfBrochures) and this value is correct.
For example for the first account (Atlantic) the sum of the creditvalues should be 8 (each brochure has a creditvalue of 1).
Can someone tell me what's wrong ?
I found the problem:
The SUM function only calculates the brochure.creditvalue for each unique brochure.brochureid. If the same brochure.brochureid exists in a different brochureOrder for the same day the brochure.creditvalue is only added once. This is caused by the relationship between brochures and brochureOrderDetails (default = wrong direction of join).
I solved this by adding this measure to the brochureOrderDetail table:
Credits = sumx(NATURALINNERJOIN(BrochureOrderDetails;Brochures);Brochures[CreditValue])
5 Replies
Change the relationship direction between Brochures and CRM_OrderLine to BOTH directions and do the same for CRM_OrderLine and CRM_Account.
