cancel
Showing results for
Did you mean:
Frequent Visitor

## 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 the 3 tables.

For example, I would like to drill down on Total Impact by Client by Activity, Flow and Impact Type (and any other variables from Table A for which we want to create a data hierarchy)

Thus multiplying Activity Amount*Flow Multiplier*Impact Multiplier for each client.

1 ACCEPTED SOLUTION
Microsoft

Hi @evdm

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,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Microsoft

Hi @evdm

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,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

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.

Microsoft

Hi @evdm

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,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper I

Did the trick! Simple easy solution! 🙂

Announcements