Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

LOOKUPVALUE and Filters

Hi, can you help me pls.

I can't find anything in this area

Еhere are 2 tables without any relationship.
I need to put the price from the first table to the second if Shop and Prod_Id matched.
But the problem is - that at the pricing table, we have different dates. and the date from the second table - should be >= maximum date - from the first. 

Thank you in advance.

 

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    6 years ago

    Hi, Anonymous 

    “LOOKUPVALUE” function is not the right direction here. We can use a measure as below to work on it.

    Price = 
VAR maxdate =
    CALCULATE (
        MAX ( Price[Date(when start work with this price)] ),
        FILTER (
            Price,
            Price[Shop] = Customer[Shop]
                && Price[Prod_id] = Customer[Prod_id]
                && Price[Date(when start work with this price)] < Customer[Date]
        )
    )
RETURN
    CALCULATE (
        MAX ( Price[Price] ),
        FILTER (
            Price,
            Price[Date(when start work with this price)] = maxdate
                && Price[Shop] = Customer[Shop]
                && Price[Prod_id] = Customer[Prod_id]
        )
)

    Here’s a sample I made:

     

    url: https://wicren-my.sharepoint.com/:u:/g/personal/michael_wicren_onmicrosoft_com/EYlU9eRqfdlPtjMm34AdjykBW3Om0nqXfSSkKF-MfSGaVA?e=EOZi6Y

     

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi, Anonymous 

    “LOOKUPVALUE” function is not the right direction here. We can use a measure as below to work on it.

    Price = 
VAR maxdate =
    CALCULATE (
        MAX ( Price[Date(when start work with this price)] ),
        FILTER (
            Price,
            Price[Shop] = Customer[Shop]
                && Price[Prod_id] = Customer[Prod_id]
                && Price[Date(when start work with this price)] < Customer[Date]
        )
    )
RETURN
    CALCULATE (
        MAX ( Price[Price] ),
        FILTER (
            Price,
            Price[Date(when start work with this price)] = maxdate
                && Price[Shop] = Customer[Shop]
                && Price[Prod_id] = Customer[Prod_id]
        )
)

    Here’s a sample I made:

     

    url: https://wicren-my.sharepoint.com/:u:/g/personal/michael_wicren_onmicrosoft_com/EYlU9eRqfdlPtjMm34AdjykBW3Om0nqXfSSkKF-MfSGaVA?e=EOZi6Y

     

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi, v-easonf-msft 

      Thank you so much. It works perfectly. 
      You rock!!!!