Different result for the calculation field between many to many tables

[briantam]

Hi,

 I set the relationship between three tables.

 SalesOrder -> SalesOrderNo. = SalesOrderDetails -> SalesOrderNo.    (Many to one)
SalesOrderDetails -> SalesOrderNo. = SalesInvoiceSODetails -> SalesOrderNo.    (Many to Many)
SalesOrderDetails -> ItemCode = SalesInvoiceSODetails -> ItemCode    (Many to Many)
SalesOrderDetails -> SeqNo = SalesInvoiceSODetails -> SOSeqNo    (Many to Many)

Example 1  : Originally the Sales Order only included four items. When I select “Sum” for the "InvQty". The total transaction has still been 4  lines

Remark :  The field “InvQty” located in the table of  “SalesInvoiceSODetails”

Example 2 :  The same Sales Order, now I select “Don’t summarize” for the "InvQty". The total transaction up to 16   lines. Actually this only has 4  lines. So can you know how to fix it ?

Thanks!
Brian  

[AllisonKennedy · Accepted solution]

Hi @briantam 

Thanks for the helpful info. 

A few things I noticed about your data model:

First, you can only have 1 active relationship between any two tables, so of those SalesOrderNo, ItemCod and SeqNo, two of them will be dotted lines and only the one solid line is working.

Second, you should only use Many to Many relationship if you absolutely need to and fully understand what you're doing. 

What you need to do is create a unique single column identifier of these columns and use that for the 1 relationship, that will hopefully be 1 to many.

You can do this in the Query Editor by clicking Transform Data in the home tab.

In the SalesOrderDetails query, use the Ctrl key to select SalesOrderNo, ItemCod and SeqNo columns, and in the Add Column tab merge the columns. 

Repeat the same merge columns in the SalesInvoiceSODetails query. 

Close and apply.

Delete all your old relationships between SalesOrderDetails and SalesInvoiceSODetails and create a Many to one relationship between these two tables instead. 

That should already solve your problem. 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[AllisonKennedy · Accepted solution]

Hi @briantam 

Thanks for the helpful info. 

A few things I noticed about your data model:

First, you can only have 1 active relationship between any two tables, so of those SalesOrderNo, ItemCod and SeqNo, two of them will be dotted lines and only the one solid line is working.

Second, you should only use Many to Many relationship if you absolutely need to and fully understand what you're doing. 

What you need to do is create a unique single column identifier of these columns and use that for the 1 relationship, that will hopefully be 1 to many.

You can do this in the Query Editor by clicking Transform Data in the home tab.

In the SalesOrderDetails query, use the Ctrl key to select SalesOrderNo, ItemCod and SeqNo columns, and in the Add Column tab merge the columns. 

Repeat the same merge columns in the SalesInvoiceSODetails query. 

Close and apply.

Delete all your old relationships between SalesOrderDetails and SalesInvoiceSODetails and create a Many to one relationship between these two tables instead. 

That should already solve your problem. 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[briantam · Accepted solution]

Hi AllisonKennedy

The problem already solved and thanks!

[amitchandak]

@briantam , Not sure why there two many many to Many relationships

 Refer how can you work with them

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-many-to-many-relationships

https://www.seerinteractive.com/blog/join-many-many-power-bi/

https://radacad.com/many-to-one-or-many-to-many-the-cardinality-of-power-bi-relationship-demystified