Return a value when lookup between 2 columns

[cjulianm]

Help please, really struggling with this one:

I have 2 unrelated tables

TableA
ACC_REF	SORT_ORDER
123
123
158
190
190
241

tCOA
SORT_ORDER	LOW	HIGH
1	120	123
2	124	150
3	151	175
4	176	191
5	192	200
6	201	250

for each row in TableA I want to return SORT_ORDER when finding the value between the LOW and HIGH bounds in Table tCOA

I have tried:

= CALCULATE(VALUES(tCOA[SORT_ORDER]),FILTER(tCOA,tCOA[LOW]<=tableA[ACC_REF]&&tCOA[HIGH]>= tableA[ACC_REF]))

[edhans]

Try this:

Sort Order = 
CALCULATE(
    MAX('Data Range'[SORT_ORDER]),
    FILTER(
	    'Data Range',
	    MAX('Table'[ACC_REF]) >= 'Data Range'[Low] && 
            MAX('Table'[ACC_REF]) <= 'Data Range'[HIGH]
	)
)

I filtered the Data Range table by the Acct_ref value, which returns 1 record, then I got the sort order. The MAX() function is simply turning those columns into scalar values, not really doing a MIN/MAX thing.

 

[cjulianm]

Thanks edhans

Just getting a single value returned. It is the corresponding value of [SORT ORDER] for the MAX value of [LOW] [HIGH]. Not sure if important but [LOW] [HIGH] not sorted in 'Data Range'

[edhans]

So it is or is not working? The sort order is 100% irrelevant for DAX. It doesn't care. It will sort for you and report if you want in the visuals, but nothing in DAX cares about the sort order. It is just records to filter and calculate on. 

[cjulianm]

No not working

[cjulianm]

It just returns the SORT_ORDER value corresponding to the maximum vale of [LOW] & [HIGH] for all values

[cjulianm · Accepted solution]

OK think I have it - only the Max before the filter is required

Sort Order = 
CALCULATE(
    MAX('Data Range'[SORT_ORDER]),
    FILTER(
	    'Data Range',
	    'Table'[ACC_REF] >= 'Data Range'[Low] && 
            'Table'[ACC_REF] <= 'Data Range'[HIGH]
	)
)

Thanks for getting me on the right track