How to look up values based on boolean logic

[johnyip]

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.

[Ahmedx · Accepted solution]

see attached
https://dropmefiles.com/5Vgli

[Ahmedx · Accepted solution]

see attached
https://dropmefiles.com/5Vgli

[johnyip · Accepted solution]

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