Forum Discussion
Solved
Create column using different table's column
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 dy...
- Try LOOKUPVALUE
- 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/
Try LOOKUPVALUE
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
- 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/