Linear Interpolation 12-30-2017 09:15 AM

[Greg_Deckler]

In the mathematical field of numerical analysis, interpolation is a method of constructing new data points within the range of a discrete set of known data points. Anyone that has done mechanical engineering knows all about interpolation. I swear my Thermo and Fluids professor, Dr. Jed Marquart was a sadist, half of our homework and tests was just interpolation.

If I can save just one engineering student time with their homework...

The assumption with linear interpolation is that the two points (x1, y1) and (x2, y2) are connected with a line. Using the two point equation of a line, we get: y - y1 = m (x - x1), where m is the slope and is calculated as m = (y2 - y1)/(x2 - x1) Thus for x3 that is between x1 and x2, we get: y3 = y1 + (x3 - x1) (y2 - y1)/(x2 - x1)

So, this quick measure performs the linear interpolation but also accounts for when linear interpolation does not need to occur (because the values are already in the set of known value). The example attached uses a thermodynamic steam table.

Pressure (MPa)  =
VAR x3 = MAX(Interpolation[Temp (C)]) //This is the known value for which you wish to interpolate another value
VAR match = CALCULATE(MAX(H2OTempSat[Pressure (MPa)]),FILTER(H2OTempSat,[Temp (C)]=x3)) //checks if it is a known value
VAR x1 = CALCULATE(MAX(H2OTempSat[Temp (C)]),FILTER(H2OTempSat,[Temp (C)]<=x3)) //low X value
VAR x2 = CALCULATE(MIN(H2OTempSat[Temp (C)]),FILTER(H2OTempSat,[Temp (C)]>=x3)) //high X value
VAR y1 = CALCULATE(MAX(H2OTempSat[Pressure (MPa)]),FILTER(H2OTempSat,[Temp (C)]<=x3)) //low Y value
VAR y2 = CALCULATE(MIN(H2OTempSat[Pressure (MPa)]),FILTER(H2OTempSat,[Temp (C)]>=x3)) //low X value
RETURN IF(NOT(ISBLANK(match)),match,y1 + (x3 - x1) * (y2 - y1)/(x2 - x1)) //if a match, return match otherwise interpolate

The generic version of this is:

Interpolated Value =
VAR x3 = MAX('Table'[Known Value])
VAR match = CALCULATE(MAX('LookupTable'[Sought Value]),FILTER('LookupTable',[Known Value]=x3))
VAR x1 = CALCULATE(MAX('LookupTable'[Known Value]),FILTER('LookupTable',[Known Value]<=x3))
VAR x2 = CALCULATE(MIN('LookupTable'[Known Value]),FILTER('LookupTable',[Known Value]>=x3))
VAR y1 = CALCULATE(MAX('LookupTable'[Sought Value]),FILTER('LookupTable',[Known Value]<=x3))
VAR y2 = CALCULATE(MIN('LookupTable'[Sought Value]),FILTER('LookupTable',[Known Value]>=x3))
RETURN IF(NOT(ISBLANK(match)),match,y1 + (x3 - x1) * (y2 - y1)/(x2 - x1))

Essentially, you have a "known value" that you are working with, like Temperature, and you are looking for a "sought value" like Pressure. The first line (x3) can be a number, measure or a column reference as shown.

eyJrIjoiNjIyY2Y5OTctZTJjNS00NzFmLWE1YzItOWRkZjRhNTA5N2Q1IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

[golf2000]

Excellent post! Thank you. What if there were multiple "items" in the same data set to calculate the interpolation for? I guess we need to add another parameter to the FILTER expressions that calculate the VARIABLES for each "item" per the current row, but I couldn't figure out how.