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

[ells69]

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

[tamerj1 · Accepted solution]

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]
        )
    )
)

[johnt75]

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.