Forum Discussion

jeyare's avatar
jeyare
Helper II
4 years ago
Solved

Find nearest value in column greater than X (variable)

Hello there, I can't find a way to get the nearest value in a column, which is greater than X (variable). The entire evaluation is within the same table. Details: I need to evaluate data stored in...
  • AllisonKennedy's avatar
    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] )

     

  • jeyare's avatar
    jeyare
    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 -)