Forum Discussion
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 | 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]))
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
6 Replies
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.
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'
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.
