Forum Discussion

cjulianm's avatar
cjulianm
Icon for Advocate I rankAdvocate I
6 years ago
Solved

Return a value when lookup between 2 columns

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 LO...
Need Help
  • cjulianm's avatar
    cjulianm
    6 years ago

    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 

edhans's avatar
edhans
Icon for Community Champion rankCommunity Champion
6 years ago

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's avatar
cjulianm
Icon for Advocate I rankAdvocate I
6 years ago

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'