What is the most performant way to check if a value is lower than a value in another table?

[arpost]

I have a set of targets and a range of values I need to check against my target table list. These are in two separate tables like below:

Range Table

Period	Start	End
0-6 Months	1	6
7-12 Months	7	12
13-24 Months	13	24

Sample Table

Item	Month
Clothes	3
Shoes	5
Food	7

With this example, I'd want to check and see if Clothes is less than or equal to the End value and return the first matching end value. These values can change, so I can't use a typical Conditional Column. I'm open to either DAX or M Query but need the result to be as follows:

Item	Month	Period
Clothes	3	0-6 Months
Shoes	5	0-6 Months
Food	7	7-12 Months

Thanks in advance.

[amitchandak · Accepted solution]

@arpost , a new column in the sample table

MAxx(filter(Range, Range[start] <= Sample[Month] && Range[end] >= Sample[month]), Range[period])

[arpost]

@amitchandak, hello again! Any chance you know of a way to accomplish this same result in Power Query? The reason I ask is I'm now needing to use this same approach on a DirectQuery dataset, and DQ doesn't support cross-table functions with calc. columns. 😭

[arpost]

Brilliant, @amitchandak! Worked like a charm. Thanks for that!