03-13-2020 22:40 PM - last edited 03-13-2020 23:07 PM
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