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 

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'

 

edhans's avatar
edhans
Icon for Community Champion rankCommunity 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
    Icon for Advocate I rankAdvocate I
    6 years ago

    No not working

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