Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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.
Solved! Go to Solution.
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
Thank you so much.
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
143 | |
75 | |
63 | |
52 | |
47 |
User | Count |
---|---|
218 | |
86 | |
64 | |
63 | |
60 |