How to Interpolate Blank Values after lookup with multiple Condition in power BI

[rahul632soni]

Hey 

I have a Table 1 which Consist of Eng Speed , Fuel Rate and Power as shown below :

Eng Speed(RPM)	Power(kW)	fuel(kg/h)
2100	221	44.8
2100	220	44.5
2100	211	42.6
2100	202	40.7
2100	193	38.9
2100	185	37.2
2100	176	35.4
2100	167	33.7
2100	158	32.0
2100	149	30.3
2100	141	28.7
2100	132	27.2
2100	123	25.6
2100	114	24.0
2100	106	22.4
2100	97	20.8
2100	88	19.1
2100	79	17.6
2100	70	16.0
2100	62	14.5
2100	53	12.9
2100	44	11.4
2100	35	9.9
2100	26	8.4
2100	18	7.0
2100	9	5.5

I have another tabel 2 which has only Eng Speed and Fuel rate as shown below :

ENG_SPEED(RPM)	ENG_FUEL_RATE(kg/H)
1098.5	7.9272
1117.5	7.4664
1000	5.0904
1000	3.4416
1000	3.456
1000	3.3912
1000	3.3696
1000	3.3696
1000	4.824
1112.5	4.8672
1000	3.5856
1000	3.5496
1000	2.88
1000	3.3408
1000	2.88
1069.5	4.0824
1058	4.0968
1173	6.7248
1194	6.7536
1160	7.7976
1352	7.812
1000	4.0104
1000	3.9384
1000	2.8584

I have lookup Power From 1st table to 2nd Table using eng speed and Fuel rate conditions however we got lots of blnak values , so for blank values of power how can we interpolate using (eng speed and fuel rate ) from table 1.

Thanks 

[mohdasaad94]

You can use the INTERPOLATE function in DAX to interpolate the blank values of Power based on the values of Eng Speed and Fuel Rate in Table 2. Here's an example DAX formula you can use:

Interpolated Power =
VAR EngSpeed = Table2[ENG_SPEED(RPM)]
VAR FuelRate = Table2[ENG_FUEL_RATE(kg/H)]
VAR Interpolated =
INTERPOLATE(
FILTER(Table1, Table1[Eng Speed(RPM)] = EngSpeed),
FuelRate,
Table1[Power(kW)]
)
RETURN
IF(ISBLANK(Table2[Power]), Interpolated, Table2[Power])

In this formula, we first define variables for Eng Speed and Fuel Rate from Table 2. Then we use the INTERPOLATE function to interpolate the Power values from Table 1 based on the Fuel Rate and the matching Eng Speed in Table 2. Finally, we use the IF and ISBLANK functions to return the interpolated Power value only for blank values in Table 2, and the original Power value for non-blank values.

[rahul632soni]

@mohdasaad94 

Its not working ,  have already tried chatGPT Solution

Thanks

[mohdasaad94 · Accepted solution]

Try this another one:

se the DAX (Data Analysis Expressions) language to create a calculated column for interpolating the blank values. Since the data you have is not uniformly distributed, you can use a combination of LOOKUPVALUE and X functions to get the desired result.

1. Create a relationship between the two tables in Power BI using the "Eng Speed" column.
2. In Table 2, create a new calculated column, named "Interpolated Power", using the following DAX formula:
   Interpolated Power =
   VAR CurrentEngSpeed = Table2[ENG_SPEED(RPM)]
   VAR CurrentFuelRate = Table2[ENG_FUEL_RATE(kg/H)]
   VAR LowerBoundPower =
   CALCULATE (
   MAX ( Table1[Power(kW)] ),
   FILTER (
   Table1,
   Table1[Eng Speed(RPM)] = CurrentEngSpeed
   && Table1[fuel(kg/h)] <= CurrentFuelRate
   )
   )
   VAR UpperBoundPower =
   CALCULATE (
   MIN ( Table1[Power(kW)] ),
   FILTER (
   Table1,
   Table1[Eng Speed(RPM)] = CurrentEngSpeed
   && Table1[fuel(kg/h)] >= CurrentFuelRate
   )
   )
   VAR LowerBoundFuelRate =
   CALCULATE (
   MAX ( Table1[fuel(kg/h)] ),
   FILTER (
   Table1,
   Table1[Eng Speed(RPM)] = CurrentEngSpeed
   && Table1[fuel(kg/h)] <= CurrentFuelRate
   )
   )
   VAR UpperBoundFuelRate =
   CALCULATE (
   MIN ( Table1[fuel(kg/h)] ),
   FILTER (
   Table1,
   Table1[Eng Speed(RPM)] = CurrentEngSpeed
   && Table1[fuel(kg/h)] >= CurrentFuelRate
   )
   )
   VAR InterpolatedValue =
   IF (
   NOT ISBLANK ( LowerBoundPower )
   && NOT ISBLANK ( UpperBoundPower ),
   DIVIDE (
   CurrentFuelRate - LowerBoundFuelRate,
   UpperBoundFuelRate - LowerBoundFuelRate
   )
   * ( UpperBoundPower - LowerBoundPower )
   + LowerBoundPower,
   BLANK ()
   )
   RETURN
   InterpolatedValue
   
   
   

This formula does the following:

• Defines the current "Eng Speed" and "Fuel Rate" values.
• Finds the nearest lower and upper bounds for "Power" and "Fuel Rate" in Table 1.
• Interpolates the "Power" value based on the difference between the current "Fuel Rate" and the lower and upper bounds.
• Returns the interpolated "Power" value or BLANK() if it cannot be calculated.

1. The new calculated column "Interpolated Power" will contain the interpolated values based on the data in Table 1.

Keep in mind that this method assumes that your data in Table 1 is sorted in ascending order by "Fuel Rate". If it's not, you should sort it first.

[rahul632soni]

@mohdasaad94 

Hey Its Working Fine but i am getting around 60% Blank Value in Iterpolated Power Column after uaing this Dax 

Can you please Help ?

Regards

Rahul