Different result for calculation field between many and many tables

[Syndicate_Admin]

Hello

I put 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 "InvQty" field located in the "SalesInvoiceSODetails" table

Example 2: On the same sales order, I now select "Do not summarize" for the "InvQty". The total transaction of up to 16 lines. Actually, this only has 4 lines. So you can know how to fix it ?

¡Gracias!
Brian

[v-jingzhang · Accepted solution]

Hi @Syndicate_Admin 

Do you notice the cross filter direction of the relationships between tables? Assume you want to filter SalesInvoiceSODetails table by SalesOrderDetails table with SalesOrderNo. column, modify its cross filter direction into Single (SalesOrderDetails filters SalesInvoiceSODetails) instead of Both. In addition, only one active relationship can exist between two tables, so select an appropriate relationship as the active relationship.

You could refer to Model relationships in Power BI Desktop article to learn more about relationships.

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.