Relationship direction problem

[livios]

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 ?

[bwarner]

Change the relationship direction between Brochures and CRM_OrderLine to BOTH directions and do the same for CRM_OrderLine and CRM_Account.

[livios]

I already tried that but this doesn't change anything 😞

[bwarner]

Then that most likely means there is something wrong with your measure. Can you post the DAX for each of your two measures?

[livios]

NrOfBrochures = COUNT(BrochureOrderDetails[BrochureId])

CreditValue = this is just a sum function of the field Brochure[CreditValue]

Is it possible this problem is caused by the fact that the Brochures[orderlineid] is the same for brochures with the same brochureId in an brochureOrder ?

[livios · Accepted solution]

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])