Measures, relationships and/or Joins?

[gunasai]

Hey guys,

I have an Order_header table (a main table) as follows through which I made a 1-many relationship with other tables. More info below.

Ord_num	Ord_cust_name	Ord_status_code
1111	ABC	Open

I have a pick detail table as follows where Item/s in the above order is being picked with its shipment number.

Note: There are many shipments for an order and because I only care about the recent shipment, I am showing only the items being picked for the most recent shipment.

Item_code	Qty_to_pick	Qty_picked	Shipment_num
1	100	0	3
2	1000	0	3
3	0	0	3

I made a 1-many relationship between the above 2 tables (because I wanted to filter out the rows in pick_det from order_header) and I made the following table as a visual on my dashboard.

Ord_num	Ord_cust_name	Item_code	Qty_to_pick	Qty_picked	Ord_status_code
1111	ABC	1	100	0	Open
1111	ABC	2	1000	0	Open
1111	ABC	3	0	0	Open

Now, my question is as follows, I have another table (order_detail) that has the following,

Ord_num	Item_code	Sales_price
1111	1	10
1111	2	20
1111	3	30
1111	4	40
1111	5	50
1111	6	60

I only care about Item_codes 1,2,3 because 4,5,6 already went in previous shipments.

Finally, I want to get the following table,

Ord_num	Ord_cust_name	Item_code	Qty_to_pick	Qty_picked	Shipment_num	Ord_status_code	Sales_price	Total_price (qty_to_pick*sales_price)
1111	ABC	1	100	0	3	Open	10	1000
1111	ABC	2	1000	0	3	Open	20	20000
1111	ABC	3	0	0	3	Open	30	0

I cannot join pick_det and order_detail tables (because Order_header is connected to both with a 1-many relationship)

How do I join pick_det and order_detail? Is there any measure I can write so I can finally make the above "total_price" calculation?

I basically have a star schema right now with order_header as the main table that has a 1-many relationship on Ord_num with both pick_det and order_detail table.

Thanks in advance. I am sorry if the question wasn't easy to understand.

[gunasai]

Thank you so much! 🙂

[v-yiruan-msft · Accepted solution]

Hi @gunasai ,

You can create two measures as below to get the total price, please find the attachment for the details.

Temp_tprice = 
SUM ( 'pick_det'[Qty_to_pick] )
    * CALCULATE (
        SUM ( 'order_detail'[Sales_price] ),
        FILTER (
            'order_detail',
            'order_detail'[Ord_num] = SELECTEDVALUE ( 'Order_header'[Ord_num] )
                && 'order_detail'[Item_code] = SELECTEDVALUE ( 'order_detail'[Item_code] )
        )
    )

Total_price = 
SUMX (
    VALUES ( 'pick_det'[Item_code] ),
    SUMX (
        GROUPBY (
            'Order_header',
            'Order_header'[Ord_num],
            'Order_header'[Ord_cust_name],
            'Order_header'[Ord_status_code]
        ),
        [Temp_tprice]
    )
)

Best Regards