Forum Discussion
Linear Interpolation with a lookup table
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
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
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.
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.