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 danextian, your solution works, but I have an additional variable I wasn't taking into account in the original question. I was wondering if you were able to help with this new wrinkle.

 

Imagine there's a third Item, with the code, ABCD. There is no rule for ABCD in Table 1, but it would it would fall back and use the ABC rule. In this case, it would be 10%, is this still possible to calculate?

 

Thank you again in advance for your help,

 

Table 1:

Item Code Cost From Cost To Uplift %
ABC $0.01  $499.99 10%
ABC $500  $999.99 8%
ABC $1,000  $1,000,000 5%
XYZ $0.01  $499.99 6%
XYZ $500  $999.99 5%
XYZ$1,000 $1,000,000 4%

 

Table 2:

Table 2:

Item Code Cost CALCULATED COLUMN / UPLIFT  %
ABC $526.24  8%
XYZ $1,124  4%
ABCD $60.85 10%
  • danextian's avatar
    danextian
    Super User
    3 years ago

    Hi Elud89 ,

     

    You can write a condition so the uplift returns 10% if the item is ABCD

    UPLIFT =
IF (
    Table1[Item Code] = "ABCD",
    0.10,
    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]
        )
    )
)

     

    • Elud89's avatar
      Elud89
      Frequent Visitor
      3 years ago

      Appreciate the response, but that would work if that was the only one, but I'm using this calculated column on a large dataset and it will have more Item Codes than just what I mentioned above. It won't be sustainable to write that many if statements.

      • danextian's avatar
        danextian
        Super User
        3 years ago

        You can use LOOKUPVALUE to check whether Table2[Item] is found in Table1. If not found, use "ABC" else  Table2[Item].

        UPLIFT = 
VAR ItemCode =
    LOOKUPVALUE ( Table1[Item Code], Table1[Item Code], Table2[Item Code] )
RETURN
    CALCULATE (
        MAX ( Table1[Uplift %] ),
        FILTER (
            ALL ( Table1 ),
            IF ( ISBLANK ( ItemCode ), "ABC", EARLIER ( Table2[Item Code] ) ) = Table1[Item Code]
                && EARLIER ( Table2[Cost] ) >= Table1[Cost From]
                && EARLIER ( Table2[Cost] ) <= Table1[Cost To]
        )
    )