Look up a number to see if it's between two numbers in diff table

[Anonymous]

Hi!

I have 2 tables below.

Table 1

Type	First	Last
A	1	3
B	7	10
C	12	15

Table 2

Number	Returned Column
2	A
12	C
5

The red column is the column I want returned.

Essentially, I want to check if the "Number column" in Table 2 is between "First" and "Last" columns in Table 1. If it is between the 2 values, I want it to return the Type column from Table 1.

Hope this makes sense, any help appreciated!

[amitchandak]

Try

maxx(filter(table2,table2[Number]>=table1[First] && table2[Number]<=table1[Last]),table1[Type])

[Anonymous]

Hi Amit - You replied on top of my DAX option... does that mean my formula does not work and that's why you gave another option?

Thanks

[Anonymous]

Hi there - 

I have created Table1 with the exact same data you provided and Table2 was created as follows:

1. Table2 was created with just 1 column with that data you have.

2. Second column is a calculated column with the following DAX code and it worked fine for me.

Please check at your end, and if it works for you as well please let me know.

The code is here:

resultColumn =

VAR numberValue = Table2[Number]

RETURN

CALCULATE(

SELECTEDVALUE(Table1[Type]),

numberValue >= Table1[First], numberValue <= Table1[Last]

)

Regards

E

[Anonymous]

Hi!

I'm fairly certain that yours would work. I am have trouble with memory and it is unable to complete the calculation for the column...

Thank you though!