Forum Discussion
Complex Lookupvalue
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
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] ) ) )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.
