Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

How to get the data from other table by filter

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?

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous,

    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's avatar
    Ashish_Mathur
    3 years ago

    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.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous,

    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's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    3 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        3 years ago

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