Create column using different table's column

[PallaviKGVG]

Hi,

I need help with formula to add new column called 'multiplier' in table "client_purchase_order_item" where this column should get generated from other column 'unit_id' of the table itself.The dynamic value of 'multiplier' column should be from 'product_units' table 'multiplier' column. 

refer to image for a relationship.

example : if( [client_purchase_order_item][unit_id] = [product_units][unit_id],[product_units] [multiplier],1)

• product_units is related to order_items as one to many  
• order_items  is related to client_purchase_order_item as many to one
• client_purchase_order_item is related to po_items_rooms as one to many

I need samething in po_item rooms table as well with same conditions. Please help me with this issue .Thanks in advance for your help.

Pallavi

[Seward12533 · Accepted solution]

Try LOOKUPVALUE

[Seward12533]

Add a calculated column to "client_purchase_order_item" 

Multiplier = RELATED(product_units[multiplier])

[PallaviKGVG]

Hi , 

when i do that on "client_purchase_order_item table", i get an error "The column 'product_units[multiplier]' either doesn't exist or doesn't have a relationship to any table available in the current context".

Since, Product_units table is not directly connected to "client_purchase_order_item" and "po_items_rooms".

Thank you 

Pallavi

[Seward12533 · Accepted solution]

Try LOOKUPVALUE

[PallaviKGVG]

Thank you Seward. That was very helpful.

The solution i tried is 

1) Multiplier = CALCULATE(
VALUES( 'krimzendev product_units'[multiplier]), FILTER('krimzendev product_units',
'krimzendev product_units'[id]='krimzendev client_purchase_order_items'[unit_id] ))

or 

2) Multiplier = LOOKUPVALUE('krimzendev product_units'[multiplier],'krimzendev product_units'[id],'krimzendev client_purchase_order_items'[unit_id])  

Both of the above worked fine. I don't see any difference in the new column values, when compared. Please let me know which is correct one, so that i can maintain the correct formulae across reports. 

Thank You 

Pallavi

[Seward12533 · Accepted solution]

Intuitively I like the 2nd but if they both work either is fine. For all I know the calculate with the filter is exactly what the model engine is doing under the covers but lookup is easier to understand when coming from a vlookul exel mindset.

If interested do some performance testing with daxstudio and see which is faster with less resources. https://www.sqlbi.com/tools/dax-studio/