Forum Discussion
Find nearest value in column greater than X (variable)
- 4 years ago
jeyare You should be able to do this with the MINX function, however the part where you want to use a slicer won't work with a calculated column: https://excelwithallison.blogspot.com/2020/09/reporting-order-of-operations.html
In order for it to update with slicers, you'd need to create them as a MEASUREs
Evaluation =
VAR _CurrentAgeIndex = SELECTEDVALUE(DataTable[Age Index])
VAR _AgeIndexDiff = SELECTEDVALUE(SlicerTable[Diff])
RETURN
MINX(FILTER(DataTable, DataTable[Age Index] >= _CurrentAgeIndex + _AgeIndexDiff ), DataTable[Age Index] )
Evaluated Weight =
VAR _CurrentAgeIndex = SELECTEDVALUE(DataTable[Age Index])
VAR _AgeIndexDiff = SELECTEDVALUE(SlicerTable[Diff])
VAR _Evaluation =
MINX(FILTER(DataTable, DataTable[Age Index] >= _CurrentAgeIndex + _AgeIndexDiff ), DataTable[Age Index] )
RETURN
MINX(FILTER(DataTable, DataTable[Age Index] = _Evaluation), DataTable[Weight] )
- 4 years ago
Thx Allison,
works great with some tunes, when I calculate MIN and MAX values to find the nearest possible difference in ABS value (+ or -)
jeyare You should be able to do this with the MINX function, however the part where you want to use a slicer won't work with a calculated column: https://excelwithallison.blogspot.com/2020/09/reporting-order-of-operations.html
In order for it to update with slicers, you'd need to create them as a MEASUREs
Evaluation =
VAR _CurrentAgeIndex = SELECTEDVALUE(DataTable[Age Index])
VAR _AgeIndexDiff = SELECTEDVALUE(SlicerTable[Diff])
RETURN
MINX(FILTER(DataTable, DataTable[Age Index] >= _CurrentAgeIndex + _AgeIndexDiff ), DataTable[Age Index] )
Evaluated Weight =
VAR _CurrentAgeIndex = SELECTEDVALUE(DataTable[Age Index])
VAR _AgeIndexDiff = SELECTEDVALUE(SlicerTable[Diff])
VAR _Evaluation =
MINX(FILTER(DataTable, DataTable[Age Index] >= _CurrentAgeIndex + _AgeIndexDiff ), DataTable[Age Index] )
RETURN
MINX(FILTER(DataTable, DataTable[Age Index] = _Evaluation), DataTable[Weight] )
Thx Allison,
works great with some tunes, when I calculate MIN and MAX values to find the nearest possible difference in ABS value (+ or -)