Compare a number with variable number ranges

I need help formulating a query that would allow me to get a Y/N output by matching a number against a list of number ranges.

Say you have a table of ranges with [Range_min] and [Range_max] columns, where each row defines a different numeric interval, and you need to determine if a given number is contained in any of the intervals defined by those rows.

The use case is numeric postcodes and deliveries. I would like to be able to have a Route entity, where I populate related Route_Ranges, and where I have actions to see all Deliveries matching the related ranges. For the action I reckon I’d use a LINKTOFILTEREDVIEW(“Deliveries”, FILTER) expression, but I don’t see how to define a filter that will act as a kind of for loop, parse every related Route_Range, see if the postcode is greater than its Range_min and smaller than its Range_max, and return true if any of the related Route_Ranges returns true.

Thoughts?

Try:

ISNOTBLANK(
  FILTER(
    "Route_Ranges",
    AND(
      ([_THISROW].[Postcode] <= [Range_Max]),
      ([_THISROW].[Postcode] >= [Range_Min])
    )
  )
)
3 Likes