Forum Discussion

MohamedKamel's avatar
MohamedKamel
Frequent Visitor
5 years ago
Solved

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.

Need Help
Tips and Tricks
  • v-easonf-msft's avatar
    v-easonf-msft
    5 years ago

    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

2 Replies

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    5 years ago

    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