Forum Discussion

ells69's avatar
ells69
Helper I
3 years ago
Solved

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

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 ...
  • tamerj1's avatar
    tamerj1
    3 years ago

    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's avatar
johnt75
Super User
3 years ago

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.