LOOKUPVALUE Range 03-13-2020 22:40 PM - last edited 03-13-2020 23:07 PM

[Greg_Deckler]

This one has been coming up a lot lately in the forums so I figured I would post a solution here. Basically, how do you lookup a value that falls between a range in another table. LOOKUPVALUE cannot be used in this way so you have to MAXX and FILTER your way to victory!

LOOKUPVALUE Range = 
  VAR __Score = MAX('Table'[Score])
  VAR __ID = MAX('Table'[ScaleID])
  VAR __Scale = 
    MAXX(
      FILTER(
        'Scales',
        'Scales'[ScaleID] = __ID &&
          __SCore >= 'Scales'[From] &&
            __Score <= 'Scales'[To]
      ),
      [Scale Result]
    )
RETURN
  __Scale

eyJrIjoiNGVkODk5OTktYzA4ZS00ODVkLWEwZDQtNGVkN2YxYzNmOWMyIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9