Search in a table for the value range in which a reference value located in another table

[PhilippeM]

Hello, (from France)

The first table contains X rows with a column of values

Value

100

1000

5000

12000

The second table has Four columns

Minimum value	Max value	Minimum scale	Max scale
0	3000	20	60
3001	6000	40	80
6001	10000	200	300
10000		350

*10000 and more

I would like to add a column to my first table which gives me the minimum applicable scale

Value	scale
100	20
1000	20
5000	40
12000	350

A big thank you for your help

Philippe

[Sahir_Maharaj · Accepted solution]

Hello @PhilippeM,

You can use the FILTER function to perform this lookup:

scale = 
VAR CurrentValue = 'Table1'[Value]
RETURN
    MINX(
        FILTER(
            'Table2',
            CurrentValue >= 'Table2'[Minimum value] &&
            (CurrentValue <= 'Table2'[Max value] || ISBLANK('Table2'[Max value]))
        ),
        'Table2'[Minimum scale]
    )