Linear Interpolation with a lookup table

[BI2018No]

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.

[Greg_Deckler]

I wrote an Interpolate measure:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Linear-Interpolation/m-p/330712

[BI2018No]

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.)

[Greg_Deckler · Accepted solution]

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

[BI2018No]

Thanks a lot for the help! I also found that setting "Cross filter direction" to "Both" solved the problem, but your suggestion her is proably better and also makes it possible to check for many conditions. 

[Greg_Deckler]

No problem, you make a good point that the inherent filter context will apply to the measure. I actually think it is better to keep measures as generic as possible in terms of the filtering as it makes them more versatile to be used in other contexts, but sometimes that is not possible.