Forum Discussion
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 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
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
6 Replies
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
Hey! It looks like this worked. I'll have to keep playing around with it, but this is greatly appreciated. Thank you!
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% 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] ) ) )
