Multiply two values based on conditions from multiple tables

[Anonymous]

Hello,

I have three tables:

Table 1 - Materials per product

ProductID	Material	Weight
1	Cotton	50
2	Polyester	30
2	Cotton	10

Table 2 - Products

ProductID	FabricType
1	Knitted
2	Woven

Table 3 - Conversion factors

Material	FabricType	Factor
Cotton	Knitted	0,5
Cotton	Woven	0,4
Polyester	Knitted	0,7

I want to do the following:

Per row in table 1 I want to multiply the weight with the correct conversion factor. The factor to be chosen depends on the material (table 1) and the fabric type (table 2). So f.i. for the first row in table one I would like to multiply 50 with 0,5 (cotton and knitted). In Excel I would make a composed value and use vlookup or in power query a composed value in both tables (f.i. cottonknitted and merge based on this). But since this example is a simplified version this will result in a lot of merging in the real scenario and slow the query down significantly. 

I feel like this should not be hard, but I'm rather new to DAX. Would like to get your input on how to solve this efficiently.

Thanks so much!

Regards,

T

[Ashish_Mathur]

Hi,

In the MPP table, write these calculated column formulas

Fabric type = RELATED(Products[FabricType])

Factor = LOOKUPVALUE(CF[Factor],CF[FabricType],MPP[Fabric Type],CF[Material],MPP[Material])

Hope this helps.

[Anonymous]

Hi Ashish,

Thanks for your help!

If I do this, then it returns an #ERROR, stating "A table with multiple values was given while a singular value was expected" (translated). 

What should i do differently?

Thanks again.

[Ashish_Mathur]

Hi,

This error means that in the CF column, there are duplicats appearing in the Fabric type and/or Material columns.  Please share a representative dataset and on that one, show your expected result so that i can share the appropriate formula.

[Ethan96]

Hi， @Anonymous 

I don't see obvious problems in the formula, I doubt it's a problem with the data.

Please check the following points

1. Ids in 'Table 2'[ProductID] are unique

2. No invalid data rows (null values) in your original model

3. There is only one corresponding factor value for each material and fabric type in ‘Table 3’

Regards,

Ethan

[lbendlin]

Contrary to what you might think this is really hard.  You need to pull down the Fabric Type from the Product Dimension into the Materials per Product fact table, then create a composite key between Material and Fabric Type and then join the Materials per Product Table with the Conversion Factors table based on that key.

Once that is done you will notice that your conversion table is missing the factor for Woven Polyester.

Which factor should you assume in that case? 1 ?