Forum Discussion

livios's avatar
livios
Frequent Visitor
7 years ago
Solved

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 g...
Need Help
  • livios's avatar
    livios
    7 years ago

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

bwarner's avatar
bwarner
Helper I
7 years ago

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

  • livios's avatar
    livios
    Frequent Visitor
    7 years ago

    I already tried that but this doesn't change anything :-(

    • bwarner's avatar
      bwarner
      Helper I
      7 years ago

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

      • livios's avatar
        livios
        Frequent Visitor
        7 years ago

        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 ?