- Power BI forums
- Updates
- News & Announcements
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Power Platform Integration - Better Together!
- Power Platform Integrations
- Power Platform and Dynamics 365 Integrations
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Custom Visuals Community Blog
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

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

Showing results for

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

- Power BI forums
- 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.