topic Re: Pattern for getting a value from another fact table based on value from current table in DAX Commands and Tips

Pattern for getting a value from another fact table based on value from current table

ells69 — Wed, 03 May 2023 08:26:06 GMT

I have two trimmed down fact tables and two dimension tables. The dimesnion tables filter both fact tables.

For my inventory (fact) table I want to retrieve the order id from the orders table. In the report there is a table showing inventory details at the level of [Key_Product_businessUnit].

My Measure is
OrderID =
VAR VAR_Key_Product_businessUnit = SELECTEDVALUE('inventory'[Key_Product_businessUnit])
VAR VAR_Result =
CALCULATE(
MAX('Orders'[OrderID]),
'Orders'[Key_Product_businessUnit] = VAR_Key_Product_businessUnit
)
RETURN
VAR_Result

Is this the best pattern to use. It seems to be really slow? There is only one row (or so the business users tell me) in Orders for each row in inventory.

JUst does not seem efficient.

Thanks

Re: Pattern for getting a value from another fact table based on value from current table

johnt75 — Wed, 03 May 2023 09:54:11 GMT

If there is only 1 order then you could use LOOKUPVALUE, e.g.

OrderID Measure = VAR VAR_Key_Product_businessUnit = SELECTEDVALUE ( 'inventory'[Key_Product_businessUnit] ) VAR VAR_Result = LOOKUPVALUE ( 'Orders'[OrderID], 'Orders'[Key_Product_businessUnit], VAR_Key_Product_businessUnit ) RETURN VAR_Result

You could also do it as a calculated column like

OrderID Column = LOOKUPVALUE ( 'Orders'[OrderID], 'Orders'[Key_Product_businessUnit], 'inventory'[Key_Product_businessUnit] )

You would probably get better performance doing it as a column.

Re: Pattern for getting a value from another fact table based on value from current table

tamerj1 — Wed, 03 May 2023 11:28:34 GMT

Hi ells69
You may try with TREATAS. It is not usually super fast but it could improve the performance in some scenarios.

OrderID = IF ( HASONEVALUE ( 'inventory'[Key_Product_businessUnit] ), CALCULATE ( MAX ( 'Orders'[OrderID] ), TREATAS ( VALUES ( 'inventory'[Key_Product_businessUnit] ), 'Orders'[Key_Product_businessUnit] ) ) )