The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hey,
I have the following two tables,
the first is pick_det and the second is order_det
joined in this way,
I want a table as follows,
Ord_num | item_code | Qty_to_pick | Qty_picked | Shipment_num | Sales_price | Total_price (Qty_to_pick*Sales_price) |
9008299 | 9079829 | 648 | 0 | 9 | 9.19 | 5955.12 |
9008299 | 9311081 | 1200 | 0 | 9 | 6.37 | 7644.00 |
9008299 | 9565414 | 0 | 0 | 9 | 8.34 | 0 |
I tried adding the sales_price column from order_det to the pick_det and got as follows,
How do I write a DAX in general to link two tables that has no relationship?
(Note: I have many orders and also many shipment_num, in this case '9' because I care only abt the most recent shipment_num)
I want to calculate the total sales price (qty_picked*sales_price of that item)
The relation appears to be on Order Number and Item Number, but you don't have an Item table.
Are you able to add an Item (product) table so that your Order_det and Pick_det can have a relationship through it?
Yes, I can add the Item (product) table and Order_det and Pick_det can have a relationship on Item_code.
But, the problem is. the item table does not have the sales_price in it.
The only way I can get the sales_price is from the Order_det table.
Do you still want me to add the Item (Product) table with relationship to both order_det and pick_det on Item_code?
You likely have two things happening.
1. When you see the Sales_Price repeating at the same wrong value right that, it's because it doesn't know what the Item is (solve that by adding the relationship to an Item table)
2. I'd guess the Data Format of your ORD_NUM in your Order_det does not match the Data Format of your ORD_NUM in your ORDER_HEADER. Double check those both are Text. It should be repeating $23.90 right now, pre Item relationship.
1. I edited my question with a new image added.
2. Ord_num columns in both Order_det and Order_header has the same data format (text).
Looks good. Well, it's definitely an issue with the Relationship still, either formatting or relationship type. You shouldn't need DAX to resolve this.
I don't have any other advice unless you're able to share the file. It seems to be pretty generic data.
Can you pleae guide me on how to share my file? I have never shared a file to the community before.
Hi,
Upload your workbook to Google Drive and share the download link here.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
93 | |
81 | |
63 | |
56 |
User | Count |
---|---|
247 | |
122 | |
110 | |
77 | |
72 |