How to get the data from other table by filter

[Quinn]

I have 2 tables, and I need DAX to help me get data from Price table to Order table. The "Customer" ,"Plant" ,"Material" in Order table must matched with Price table , and "ETD" should within valid from and valid to, then we can find the correct price.

I try to use below DAX it can get the correct data, but my team member said we cannot use MAXX function, since it's to find the maximum value. Is there any other Dax can help me to get the result?

[v-shex-msft · Accepted solution]

Hi @Quinn,

In fact, Maxx or other aggregated functions can be used to lookup value from another table.
You can modify the conditions to get one result and it should be the value that you want, or it will return the aggregate value from results that match with conditions.

formula =
VAR currCustomer =
    SELECTEDVALUE ( Order[Customer] )
VAR currPlant =
    SELECTEDVALUE ( Order[Plant] )
VAR currPN =
    SELECTEDVALUE ( Order[PN] )
VAR currDate =
    MAX ( Order[ETD] )
RETURN
    MAXX (
        FILTER (
            ALLSELECTED ( 'Price' ),
            [Customer] = currCustomer
                && [Plant] = currPlant
                && [Material] = currPN
                && AND ( currDate >= [Valid From], currDate <= [Valid To] )
        ),
        [Condition Amount]
    )

Regards,

Xiaoxin Sheng

[Ashish_Mathur · Accepted solution]

Hi,

Write this calculated column formula in the Order Table

Correct Price = calculate(max(Price[Condition amount]),filter(Price,Price[Customer]=earlier(Order[Customer])&&Price[Plant]=earlier(Order[Plant])&&Price[Material]=earlier(Order[PN])&&Price[Valid from]<=earlier(Order[ETD])&&Price[Valid To]>=earlier(Order[ETD])))

Hope this helps.

[Quinn]

Hi,

thanks for your help.

May I know what's the meaning of "earlier", why we should put it in each item first? and if I want to change the "Price[Valid from]<=earlier(Order[ETD])" to Price[Valid from] <= today (), how to write the dax?

Thank you.

[Ashish_Mathur]

You are welcome.  EARLIER() can be interpeted as "current row".  Write it exactly that way.

[v-shex-msft · Accepted solution]

Hi @Quinn,

In fact, Maxx or other aggregated functions can be used to lookup value from another table.
You can modify the conditions to get one result and it should be the value that you want, or it will return the aggregate value from results that match with conditions.

formula =
VAR currCustomer =
    SELECTEDVALUE ( Order[Customer] )
VAR currPlant =
    SELECTEDVALUE ( Order[Plant] )
VAR currPN =
    SELECTEDVALUE ( Order[PN] )
VAR currDate =
    MAX ( Order[ETD] )
RETURN
    MAXX (
        FILTER (
            ALLSELECTED ( 'Price' ),
            [Customer] = currCustomer
                && [Plant] = currPlant
                && [Material] = currPN
                && AND ( currDate >= [Valid From], currDate <= [Valid To] )
        ),
        [Condition Amount]
    )

Regards,

Xiaoxin Sheng