Forum Discussion
Linear Interpolation with a lookup table
Hello! I'm new to Power BI, but I try my best to convert some of my old Excel sheets to Power BI.
I want to perform linear interpolation based on values in a seperate table. This table describes many different curves, so I have added an ID column. Then i have a table with time series with an ID and a X value. What i want is to create a new column in this table for the interpolated Y value (based on the right ID and the cuves in the other table)
This is the table with the known X and Y values
| ID | X | Y |
| 0 | 1 | 0 |
| 0 | 2 | 4 |
| 0 | 3 | 10 |
| 0 | 4 | 18 |
| 0 | 5 | 28 |
| 1 | 1 | 12 |
| 1 | 2 | 16 |
| 1 | 3 | 22 |
| 1 | 4 | 30 |
| 1 | 5 | 40 |
And here is the Time serie table where I want to create a new Y' column and get the interpolated value for Y returned from the table above. (X is known)
| ID | X | Y' |
| 0 | 1 | 0 |
| 0 | 2 | 4 |
| 0 | 2,5 | 7 |
| Next ID | ||
| 1 | 4,5 | 35 |
In Excel this can be done combining a set og Excel formulas and also using a function in VBA. Is this possible to do in Power BI?
Very grateful if anyone can guide me to a solution here.
So, if I understand what you are saying, you want sensor ID=0 to only interpolate over your known entities table with a corresponding ID=0 and ID=1 should only interpolate over ID=1 known entities, correct? Let me know if that is correct, shouldn't be difficult at all to modify the formula. So, using my original formula, you could probably do something like below (which assumes that you have unrelated tables that both have an ID column
Pressure (MPa) = VAR x3 = MAX(Interpolation[Temp (C)]) //This is the known value for which you wish to interpolate another value VAR __id = MAX(Interpolation[ID] // current ID VAR match = CALCULATE(MAX(H2OTempSat[Pressure (MPa)]),FILTER(H2OTempSat,[Temp (C)]=x3 && [ID]=__id)) //checks if it is a known value VAR x1 = CALCULATE(MAX(H2OTempSat[Temp (C)]),FILTER(H2OTempSat,[Temp (C)]<=x3 && [ID]=__id)) //low X value VAR x2 = CALCULATE(MIN(H2OTempSat[Temp (C)]),FILTER(H2OTempSat,[Temp (C)]>=x3 && [ID]=__id)) //high X value VAR y1 = CALCULATE(MAX(H2OTempSat[Pressure (MPa)]),FILTER(H2OTempSat,[Temp (C)]<=x3 && [ID]=__id)) //low Y value VAR y2 = CALCULATE(MIN(H2OTempSat[Pressure (MPa)]),FILTER(H2OTempSat,[Temp (C)]>=x3 && [ID]=__id)) //low X value RETURN IF(NOT(ISBLANK(match)),match,y1 + (x3 - x1) * (y2 - y1)/(x2 - x1)) //if a match, return match otherwise interpolate
5 Replies
I wrote an Interpolate measure:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Linear-Interpolation/m-p/330712
This was a great exampel showing interpolation made simpel in Power BI. Is is possible to add som kind of filter? For example if we have many different temp. sensors with an ID, and the H2OtempSat table have the same ID column and temp/pressure for all temp. sensors? (two different temp. sensors can then have different pressure for the same temp.)
So, if I understand what you are saying, you want sensor ID=0 to only interpolate over your known entities table with a corresponding ID=0 and ID=1 should only interpolate over ID=1 known entities, correct? Let me know if that is correct, shouldn't be difficult at all to modify the formula. So, using my original formula, you could probably do something like below (which assumes that you have unrelated tables that both have an ID column
Pressure (MPa) = VAR x3 = MAX(Interpolation[Temp (C)]) //This is the known value for which you wish to interpolate another value VAR __id = MAX(Interpolation[ID] // current ID VAR match = CALCULATE(MAX(H2OTempSat[Pressure (MPa)]),FILTER(H2OTempSat,[Temp (C)]=x3 && [ID]=__id)) //checks if it is a known value VAR x1 = CALCULATE(MAX(H2OTempSat[Temp (C)]),FILTER(H2OTempSat,[Temp (C)]<=x3 && [ID]=__id)) //low X value VAR x2 = CALCULATE(MIN(H2OTempSat[Temp (C)]),FILTER(H2OTempSat,[Temp (C)]>=x3 && [ID]=__id)) //high X value VAR y1 = CALCULATE(MAX(H2OTempSat[Pressure (MPa)]),FILTER(H2OTempSat,[Temp (C)]<=x3 && [ID]=__id)) //low Y value VAR y2 = CALCULATE(MIN(H2OTempSat[Pressure (MPa)]),FILTER(H2OTempSat,[Temp (C)]>=x3 && [ID]=__id)) //low X value RETURN IF(NOT(ISBLANK(match)),match,y1 + (x3 - x1) * (y2 - y1)/(x2 - x1)) //if a match, return match otherwise interpolate
