Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Galleries
- Quick Measures Gallery
- Linear Interpolation

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Linear Interpolation

12-30-2017
09:15 AM

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

Latest book!:

DAX is easy, CALCULATE makes DAX hard...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

04-06-2019
05:23 PM

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.