cancel
Showing results for
Did you mean:
Solution Specialist

## How to look up values based on boolean logic

Sample data

Table A

 Shop Value From_year To_year A 12 2019 2021 A 345 2022 2023

Table B

 Shop Year A 2020 A 2023

I want to create a calculated column [Value_looked_up] in Table B, with the expected result as below:

Table B

 Shop Year Value_looked_up A 2020 12 A 2023 345

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.

2 ACCEPTED SOLUTIONS
Solution Sage
Solution Specialist

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

2 REPLIES 2
Solution Sage
Solution Specialist

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