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 

edhans's avatar
edhans
Community Champion
6 years ago

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

No not working

  • 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