DAX Formula to for two related tables using a conversion table

[DekkerNick]

I have to tables. See example below for ItemNos 1145 and 4721. 

Table 1: ProductionOrderComponent

prodOrderNo	prodOrderLineNo	lineNo	expectedQtyBase	expectedQuantity	itemNo	qtyPerUnitOfMeasure	quantity	unitOfMeasureCode
PRD-ORD23-000011	10000	20000	750	3	1145	250	0,00273	BL
PRD-ORD23-000012	10000	20000	750	3	1145	250	0,00258	BL
PRD-ORD23-000012	10000	80000	1800	1800	4721	1	1,547999997	GRS
PRD-ORD23-000013	10000	20000	1000	4	1145	250	0,00312	BL

Tables 2: ItemUnitofMeasure (this is a conversion table to converted the quantity to KG or vice versa.

itemNo	code		qtyPerUnitOfMeasure
1145	BL		250
1145	KG		1
1145	TON		1000
4721	GRS		1
4721	KG		1000

In the table Production Order Component I would like to add a new columns which shows the quantity in KG. So, for Item 1145 the quantity in amount should be ProductionOrderComponent[Expected Quantity] * ItemUnitofMeasure[qtyperunitofmeasure].[BL] = 4 * 250 = 1000 kg. This formula converts the 4 BL of 250 to 1000 KG based on the conversion table (ItemUnitOfMeasure). 

For Itemno 4721, it is a different story. This scenario states that 1800 GRS is used in the production process. However, I would like to convert this to KG. I can only do this by ProductionOrderComponent[Expected Quantity] / ItemUnitofMeasure[qtyperunitofmeasure].[KG] = 1800 / 1000 = 1,8 KG. 

I have difficulties with the formula to relate both tables and to make sure the right path is chosen, and therefore, the correct conversion factor. 

[DekkerNick]

Hi @ERD, 

it feels that I am almost there! However, I get empty values. 

So therefore, I have two questions.

1. What did you do in the itemUnitOfMeasure table to convert GRS with KG? 
2. Why do you use the MAX formula inside your Calculate in the conversion variable? 

[ERD]

Hi,

1. Math? 🙂. Just changed the initial data manually in ItemUnitofMeasure table to be consistent with your goal - convertion to/from KG.

2. You need to use some aggregation function here to access the value inside CALCULATE.

[DekkerNick]

Hi @ERD, 

That's clear. For sure I have created a conversion to KG column with the following formula and this works; 

Conversion to KG = IF(itemUnitOfMeasure[code] = "GRS", itemUnitOfMeasure[qtyPerUnitOfMeasure] / 1000, itemUnitOfMeasure[qtyPerUnitOfMeasure])

Thanks for the solution. 

[ERD · Accepted solution]

Hi @DekkerNick , it would be better if you change your ItemUnitofMeasure table to be consistent with your goal - convertion to/from KG. In this case it will be much easier to calculate.

So for BL it's ok. But for GRS since you have to divide, then to change it to multiplication, you need to put 1 KG = 0.001 GRS. 

Column:

KG =
VAR qty = ProductionOrderComponent[expectedQuantity]
VAR itemNo = ProductionOrderComponent[itemNo]
VAR unit = ProductionOrderComponent[unitOfMeasureCode]
VAR conversion =
    CALCULATE (
        MAX ( ItemUnitofMeasure[qtyPerUnitOfMeasure] ),
        ItemUnitofMeasure[itemNo] = itemNo,
        ItemUnitofMeasure[code] = unit
    )
RETURN
    qty * conversion