Forum Discussion

DarylRob's avatar
DarylRob
Icon for Helper I rankHelper I
4 years ago
Solved

Lookup closest value in table from parameter

So I've got a what if parameter where the user enters a value, I then want to lookup this value in a table of 1000 values and then return another column from the row with the closest value

I.e the table is

1100,000
2350,000
320,000
4478,000

 

The user would enter 290,000 and it return 2. Is this possible?

 

 

Need Help
  • MFelix's avatar
    MFelix
    4 years ago

    Hi DarylRob ,

     

    You can achieve this by using a difference between the what if and the table if you setup somehting similart to this:

     

    Closest value =
VAR temptable =
    TOPN (
        1,
        ADDCOLUMNS (
            Table,
            "@Difference", ABS ( Table[Value] - 'Parameter'[Parater Value] )
        ),
        [@Difference], ASC
    ) 
RETURN
    MINX ( temptable, Table[ID] )

     

2 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    4 years ago

    Hi DarylRob ,

     

    You can achieve this by using a difference between the what if and the table if you setup somehting similart to this:

     

    Closest value =
VAR temptable =
    TOPN (
        1,
        ADDCOLUMNS (
            Table,
            "@Difference", ABS ( Table[Value] - 'Parameter'[Parater Value] )
        ),
        [@Difference], ASC
    ) 
RETURN
    MINX ( temptable, Table[ID] )