I want to create a calculated column [Value_looked_up] in Table B, with the expected result as below:
The result is to perform lookup with [shop], returning the relevant [value] when 'Table B'[Year] >= 'Table A'[From_year] and Table B'[Year] <= 'Table A'[To_year].
Any ideas how to do that? I tried LOOKUPVALUE(), but this function seems won't allow boolean logic in its syntax.
Much appreciated for the help.
Go to Solution.
View solution in original post
Thanks for the help, your answer has reminded me of how powerful CALCULATE() is supposed to be.
Was looking into LOOKUPVALUE() while forgetting this can be done by CALCULATE().
And my since my actual data is not numeric, for those who wish to lookup text, please try the following:
Value = CALCULATE(MAX('Tab_A'[Value]),
FILTER('Tab_A',[To year]>=[Year] && [From year]<=[Year])
Take a look at the September 2023 Power BI update to learn more.
Join Microsoft Reactor and learn from developers.