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

2 Replies

  • 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.

  • tamerj1's avatar
    tamerj1
    Community Champion
    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]
        )
    )
)