DAX to calculate a column from two tables with no relationship.

[gunasai]

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)

[cassidy]

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?

[gunasai]

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? 

[cassidy]

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.

[gunasai]

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).

[cassidy]

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.

[gunasai]

Can you pleae guide me on how to share my file? I have never shared a file to the community before.

[Ashish_Mathur]

Hi,

Upload your workbook to Google Drive and share the download link here.

[cassidy]

Refer here 

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...