Forum Discussion

cjulianm's avatar
cjulianm
Advocate 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 

cjulianm's avatar
cjulianm
Advocate I
6 years ago

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

cjulianm's avatar
cjulianm
Advocate I
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