Forum Discussion

Elud89's avatar
Elud89
Frequent Visitor
4 years ago
Solved

Complex Lookupvalue

Hi PowerBI Community,   I am trying to join two tables and populate a unique value based on a set of conditions. I would normally use lookupvalue to accomplish this, but there are multiple answers ...
Need Help
Show and Tell
Tips and Tricks
  • danextian's avatar
    danextian
    4 years ago

    Hi Elud89 ,

     

    I would use CALCULATE instead of LOOKUPVALUE for your use case.

    Sample formula:

    UPLIFT =
CALCULATE (
    MAX ( Table1[Uplift %] ),
    FILTER (
        ALL ( Table1 ),
        Table1[Item Code] = EARLIER ( Table2[Item Code] )
            && EARLIER ( Table2[Cost] ) >= Table1[Cost From]
            && EARLIER ( Table2[Cost] ) <= Table1[Cost To]
    )
)

    sample result

     

danextian's avatar
danextian
Super User
4 years ago

Hi Elud89 ,

 

I would use CALCULATE instead of LOOKUPVALUE for your use case.

Sample formula:

UPLIFT =
CALCULATE (
    MAX ( Table1[Uplift %] ),
    FILTER (
        ALL ( Table1 ),
        Table1[Item Code] = EARLIER ( Table2[Item Code] )
            && EARLIER ( Table2[Cost] ) >= Table1[Cost From]
            && EARLIER ( Table2[Cost] ) <= Table1[Cost To]
    )
)

sample result

 

Elud89's avatar
Elud89
Frequent Visitor
4 years ago

Hey! It looks like this worked. I'll have to keep playing around with it, but this is greatly appreciated. Thank you!