cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Linear Interpolation with a lookup table

Hello,

I ran into a problem doing linear interpolation in Power BI. There are two tables, Link by Link emission rate and Link by Link summary table. The Emission rate table is a lookup table with emission rates provided for particular average speed, time of day and season. I need to get a linearly interpolated emission values for the given average speed, time of day and season.

These are the measures I created, I am running into syntax error on the first measure Lower Speed bin itself.  The error reads ' A single value for column Time of day in table link by link summary cannot be determined'.

```Lower speed bin =
CALCULATE (
FILTER (
),
FILTER (
),
FILTER (
)
)

```
```Upper speed bin =
CALCULATE (
FILTER (
),
FILTER (
),
FILTER (
)
)```

```THC Emission Lower =

CALCULATE(

FILTER (

))```

`THC Emission Upper = CALCULATE(   MIN( 'Link by Link Emission Rates Table'[THC (g/mile)]),   FILTER (        'Link by Link Emission Rates Table',        'Link by Link Emission Rates Table'[Average speed] = 'Link by Link Summary'[Upper speed bin]))`

```Interpolation Fraction :=
DIVIDE (
[Average Speed] - [Lower Speed bin],
[Upper Speed bin] - [Lower Speed bin]
)```
```THC interpolated :=
[THC Emission lower]
+ [Interpolation Fraction]
* ( [THC Emission Upper] - [THC ELower] )```

1 ACCEPTED SOLUTION

Accepted Solutions
Frequent Visitor

## Re: Linear Interpolation with a lookup table

Hey,

Thanks for all the help. I found a solution. I created a calculated column instead of a measure with intermediate variables. It worked as expected. Below is the working code.

```THC emissions (g/mile) =

VAR Lowerbin = CALCULATE (
FILTER (
)
)
VAR upperbin =
CALCULATE (
FILTER (
)
)

VAR THCEmissionLower =
CALCULATE (
FILTER (
=Lowerbin
)
)

VAR THCEmissionUpper =
CALCULATE (
FILTER (
)
)

VAR InterpolationFraction =
DIVIDE ( 'Link by Link Summary'[Average Speed] - Lowerbin , Upperbin - Lowerbin)

VAR THCinterpolated = THCEmissionlower
+ InterpolationFraction
* ( THCEmissionUpper - THCEmissionLower )

RETURN
THCinterpolated```
3 REPLIES 3
Super Contributor

## Re: Linear Interpolation with a lookup table

Hi @rohitj,

First if you have multiple conditions in the FILTER(), there's no need to write duplicate FILTER(). Simply:

```Lower speed bin =
CALCULATE (
FILTER (
)
)```

Then the error is because that in FILTER(), you are filtering 'Link by Link Emission Rates Table'. So you can only call the columns in emission rates table. However, you are also calling the table 'Link by Link Summary' in FILTER(). That's why it returns an error.

And if you want to call different table columns in FILTER(). You need to do aggregate. Something like:

```Lower speed bin =
CALCULATE (
FILTER (
)
)```

As in your scenario, what's your desired result? Could you please shares us some sample data and the corresponding desired result if possible? So that we can understand your requirement more clearly and provide some proper suggestions. Also, if you can share us your pbix file. It'll help us more.

Thanks,
Xi Jin.

Frequent Visitor

## Re: Linear Interpolation with a lookup table

Hey @v-xjiin-msft,

Thanks for the reply. My desired result is to obtain a emission rate for each row in link by link summary table according to the time of day, season and average speed, by referring to the link by link emission rate table. The average speed in the link by link emission rate table are for particular speeds. So, if the speed is in between, linear interpolation needs to be done to return a emission value.

The Pbix file can be found in the following link. https://1drv.ms/u/s!AglC4z4HVNowokNPJkR7gj0YmAL3

Thanks

Frequent Visitor

## Re: Linear Interpolation with a lookup table

Hey,

Thanks for all the help. I found a solution. I created a calculated column instead of a measure with intermediate variables. It worked as expected. Below is the working code.

```THC emissions (g/mile) =

VAR Lowerbin = CALCULATE (
FILTER (
)
)
VAR upperbin =
CALCULATE (
FILTER (
)
)

VAR THCEmissionLower =
CALCULATE (
FILTER (
=Lowerbin
)
)

VAR THCEmissionUpper =
CALCULATE (
FILTER (
)
)

VAR InterpolationFraction =
DIVIDE ( 'Link by Link Summary'[Average Speed] - Lowerbin , Upperbin - Lowerbin)

VAR THCinterpolated = THCEmissionlower
+ InterpolationFraction
* ( THCEmissionUpper - THCEmissionLower )

RETURN
THCinterpolated```

Announcements

#### Community Highlights

Find out what's new in the Power BI Community!

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 96 members 1,548 guests
Recent signins: