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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
MohamedKamel
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.

 

MohamedKamel_0-1615361825199.png

 

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
v-easonf-msft
Community Support
Community Support

Hi, @MohamedKamel 

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

View solution in original post

2 REPLIES 2
MohamedKamel
Frequent Visitor

Thank you so much.

v-easonf-msft
Community Support
Community Support

Hi, @MohamedKamel 

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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