Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

DAX Across Multiple Many-to-Many Table Relationships

Hi all,    I would please like some assistance with the below use case for DAX.   I have 3 tables with a many-to-many relationship and I want to do calculations where I multiply the values across...
Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi Anonymous 

    You may create 2 measures as below:

    Measure = SUM(TableA[Activity Amount])*SUM(TableB[Flow Multiplier])*SUM(TableC[Impact Multiplier])
    Measure 2 = SUMX(TableB,[Measure])

    Regards,

v-cherch-msft's avatar
v-cherch-msft
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago

Hi Anonymous 

It seems you may create more and more relationships and then create the measure.If it is not your case,please explain more about your expected output with your sample data.

Measure = SUM(TableA[Activity Amount])*SUM(TableB[Flow Multiplier])*SUM(TableC[Impact Multiplier])

Regards,

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi v-cherch-msft , thank you for your response. 

     

    That makes sense and that is how I set it up as well and that DAX is working. 

     

    Can I please ask just some direction with the below, still learning DAX. So in Excel, I have the below data. For the Total Impact by my clients I have the formula in Cell B16 with the following formula :

     

    =(C8*I8*O8)+(C8*I9*O10)+(C9*I10*O8)+(C9*I11*O10)+(C8*I8*O9)+(C8*I9*O11)+(C9*I10*O9)+(C9*I11*O11)

     

    So basically I multiply the Activity Amount In table A with the corresponding activities flow multiplier in Table B and with the corresponding Impact Multiplier in Table C. I cannot replicate my answer in Excel in Power BI. 

     

     

    • v-cherch-msft's avatar
      v-cherch-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      7 years ago

      Hi Anonymous 

      You may create 2 measures as below:

      Measure = SUM(TableA[Activity Amount])*SUM(TableB[Flow Multiplier])*SUM(TableC[Impact Multiplier])
      Measure 2 = SUMX(TableB,[Measure])

      Regards,

      • eeew00's avatar
        eeew00
        Icon for Helper I rankHelper I
        5 years ago

        Did the trick! Simple easy solution! 🙂