KNOWLEDGE BASE

## Find Closest Matching Number in Data Set

Published: 23 May 2017

### Question

How to find the closest matching number in the data set to a selection.

For example, if you should be able to select any number from a range, but there are missing ID numbers in the data, then show the closest matching ID number.

### Environment

Tableau Desktop

The attached example workbook uses sample data set to demonstrate the following instructions:
1. Click the down arrow next to Dimensions in the data pane and select Create Parameter
2. In the Create Parameter dialog box, do the following and click OK:
•  In the Name text box, type a name. In this example the parameter is named Select an ID.
•  Under Properties, in the Data type list, select Integer.
•  Under Allowable values, select Range.
•  Set the Minimum and Maximum.
3. Right-click [Select an ID] in the data pane and select Show Parameter Control.
4. Create a calculated field with a name like Smallest Difference with a calculation similar to the following:
`{ FIXED : MIN(ABS([Select an ID] - [ID]))}`

The above calculation uses the level of detail (LOD) function FIXED to return the smallest absolute difference between the selected [Select an ID] value and the data set value [ID] in the whole data set.

5. Create a calculated field with a name like "New ID" with a calculation similar to the following:\
`IF [ID] = [Select an ID] THEN [ID] ELSEIF [Select an ID] - [ID] = [Smallest Difference] THEN [ID] END`

The above IF statement will return the data set value of [ID] if there is a value that matches the user selection. If there is not a matching value, then the IF statement will return the value of  [ID] whose difference matches the smallest absolute difference over the whole data set.

6. Drag [New ID] onto the Rows shelf.
7. Right-click Null and select Exclude.