PowerPivot LOOKUPVALUE

[gavs73]

Hi

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

Thanks for your help.
Gav

 

[gavs73]

I've also tried this...

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

 

[akice]

These may help:
Banding in DAX - Excelerator BI
https://www.sqlbi.com/articles/grouping-transactions-by-age-of-customers-in-dax/
https://www.sqlbi.com/tv/dax-patterns-banding-new-vs-old-many-to-many/