Forum Discussion
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.
Thanks in advance.
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,
4 Replies
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,
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.
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,