Check if a value is included in an interval of another table

[Ramusoc]

Hello everyone,

I want to know a value from Table 1 in which interval (Table 2) it exists and get the category from Table 2, as the example below: 

Table 1 

Client |  Sales

1        |  100

2        |   50

3        |   25

4        |   2

Table 2:

min   |   max |  Category

0       |   20    |   A

21     |   60    |   B

61     |   100  |   C

The output should be :

Client |  Sales   | Category

1        |  100      | A

2        |   50       | B

3        |   25       | B

4        |   2         | C

Could you please help me on that issue.

Thanks in advance.

[mahoneypat · Accepted solution]

Not sure if you want a column or measure expression, but here is a column one.

Category =
VAR thissales = Table1[Sales]
RETURN
    MINX (
        FILTER ( Table2, Table2[min] <= thissales && Table2[max] >= thissales ),
        Table2[Category]
    )

Pat

[Ramusoc]

Hey, 

It worked. Thanks a lot !!

[lbendlin]

In DAX you would use GENERATESERIES and INTERSECT

In Power Query you would use List.Generate and List.Contains

What's your preference?

[Ramusoc]

Hey Ibendlin,

Thank you for your interest.

I'd prefer DAX, but how am i going to use generateseries and intersect?