Forum Discussion

johnyip's avatar
johnyip
Solution Sage
3 years ago
Solved

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...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • johnyip's avatar
    johnyip
    3 years ago

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

     

     

  • johnyip's avatar
    johnyip
    Solution Sage
    3 years ago

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