Forum Discussion
Solution SageHow 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.
see attached
https://dropmefiles.com/5VgliThanks 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
- Ahmedx
Super User
see attached
https://dropmefiles.com/5Vgli
johnyipThanks 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]) )