cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Incorrect Division Measure Calculation With Inputs From Two Different Tables

Hi All,

I have a division measure that is supposed to get the numerator from table 1 and the denominator from table 2. I created a many to many active relationship between two compound keys in both tables.

The compound key in table 1 is a newly created column in power BI with equation 'Compound Key = Table1[Date]&"-"&Table1[Area]&"-"&Table1[Line]&"-"&Table1[Machine]'; and the second compound key in table 2 is also a newly created column in power BI with equation 'Compound Key = Table2[Date]&"-"&Table2[Area]&"-"&Table2[Line]&"-"&Table2[Machine]'.

Both compound keys are a combination of four columns (date, area, line and machine) with exact same data names in both tables so when any criteria from these four columns is selected the division measure calculates per the selected criteria only.

However when selecting more than one criteria (such as date and area) the measure does not calculate correctly at all with the division equation '% Breakdown = DIVIDE(SUM('Table1'[Breakdown Time(hours)]),SUM('Table2'[Loading Time (hours)]))'.

Can someone please help and if the issue is still unclear we can have a quick call to explain live better while sharing my screen with you.

Thanks.

1 ACCEPTED SOLUTION
Community Support

It is not recommended to create  many-to-many table relationship between two tables.

You can try to insert another intermediate table "Table3" about "Compound keys", which contains 5 columns (compoundkeys, date, area, line, machine)

``Table3 = DISTINCT(UNION(SUMMARIZE(Table1,Table1[Date],Table1[Area],Table1[Machine],Table1[line],Table1[CompoundKey]),SUMMARIZE(Table2,Table2[Date],Table2[Area],Table2[Machine]Table2[line],Table2[Compound Key])))``

After establishing  one-to-many relationship with the original two tables through the key "Compound keys", then apply the fields in Table 3 to slicers to filter other  tables' data.

Best Regards,
Community Support Team _ Eason

2 REPLIES 2
Frequent Visitor

Thank you so much.

Community Support

It is not recommended to create  many-to-many table relationship between two tables.

You can try to insert another intermediate table "Table3" about "Compound keys", which contains 5 columns (compoundkeys, date, area, line, machine)

``Table3 = DISTINCT(UNION(SUMMARIZE(Table1,Table1[Date],Table1[Area],Table1[Machine],Table1[line],Table1[CompoundKey]),SUMMARIZE(Table2,Table2[Date],Table2[Area],Table2[Machine]Table2[line],Table2[Compound Key])))``

After establishing  one-to-many relationship with the original two tables through the key "Compound keys", then apply the fields in Table 3 to slicers to filter other  tables' data.

Best Regards,
Community Support Team _ Eason

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.