LOOKUPVALUE Range

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