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,

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
Microsoft 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
    Helper I
    5 years ago

    Did the trick! Simple easy solution! 🙂