PowerPivot LOOKUPVALUE

I'd appreciate any pointers on this, I need to look up in PP a value based on a range in another PP Table.

I want to return 'BAND' based on where Revenue in the first table falls between High and Low Band Values in the Band Table.

=LOOKUPVALUE(Band[Band],Band[Low],>=[Revenue],Band[High],<=[Revenue])

The Band Table is set up as

Band 0-100 Low 0 High 100
Band 101-200 Low 101 High 200
etc

I've also tried this...

=FILTER(Band[Band],[Revenue]>=Band[Low],[Revenue]<=Band[High])

Thanks for your help. Gav

Solution

LookupValue doesn't support conditional evaluations, instead you can use a FILTER function and FIRSTNONBLANK function to get the right Band[Band].

Create a calculated column in the Combined table using this expression:

LookupBand =
CALCULATE (
    FIRSTNONBLANK ( Band[Band], 0 ),
    FILTER (
        Band,
        [Low] <= EARLIER ( Combined[Revenue] )
            && [High] >= EARLIER ( Combined[Revenue] )
    )
)