Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as 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 C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
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
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as 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 C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Hi AllisonKennedy
The problem already solved and thanks!
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |