Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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.

Table.PNG

 

Thanks in advance. 

1 ACCEPTED SOLUTION

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

1.png

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.

View solution in original post

4 REPLIES 4
v-cherch-msft
Employee
Employee

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

1.png

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.
Anonymous
Not applicable

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. 

 

IM2.PNG

 

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

1.png

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.

Did the trick! Simple easy solution! 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.