Complex Lookupvalue

[Elud89]

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 so it does not work.

In Table 1, I have an Item Code and 3 seperate uplift values based on a numeric range. In Table 2, we have the same Item Code as well as the actual value of the unit. I want to calculate the Uplift % in Table 2, based on the inputs from Table 1. 

Any up with creating a calculated column that does the bolded action in Table 2 below is greatly appreciated.

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:

Item Code	Cost	CALCULATED COLUMN / UPLIFT  %
ABC	$526.24	8%
XYZ	$1,124	4%

Hopefully that makes sense, let me know if I can clarify anything.

Thanks

[danextian · Accepted solution]

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]

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]

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]

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]

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]
        )
    )

[Elud89]

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