Linear Interpolation with a lookup table

[rohitj]

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. 

 Link by Link emission rate tableLink by Link Summary table

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 (
    MAX ( 'Link by Link Emission Rates Table'[Average speed] ),
    FILTER (
        'Link by Link Emission Rates Table',
        'Link by Link Emission Rates Table'[Time of day]
            = 'Link by Link Summary'[Time of day]
    ),
    FILTER (
        'Link by Link Emission Rates Table',
        'Link by Link Emission Rates Table'[Season] = 'Link by Link Summary'[Season]
    ),
    FILTER (
        'Link by Link Emission Rates Table',
        'Link by Link Emission Rates Table'[Average speed]
            <= 'Link by Link Summary'[Average speed]
    )
)

Upper speed bin =
CALCULATE (
    MIN ( 'Link by Link Emission Rates Table'[Average speed] ),
    FILTER (
        'Link by Link Emission Rates Table',
        'Link by Link Emission Rates Table'[Time of day]
            = 'Link by Link Summary'[Time of day]
    ),
    FILTER (
        'Link by Link Emission Rates Table',
        'Link by Link Emission Rates Table'[Season] = 'Link by Link Summary'[Season]
    ),
    FILTER (
        'Link by Link Emission Rates Table',
        'Link by Link Emission Rates Table'[Average speed]
            <= 'Link by Link Summary'[Average speed]
    )
)

THC Emission Lower = 

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'[Lower speed bin]

))

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

[v-xjiin-msft]

Hi @rohitj,

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

Lower speed bin =
CALCULATE (
    MAX ( 'Link by Link Emission Rates Table'[Average speed] ),
    FILTER (
        'Link by Link Emission Rates Table',
        'Link by Link Emission Rates Table'[Time of day] = xxx
            && 'Link by Link Emission Rates Table'[Season] = xxx
            && 'Link by Link Emission Rates Table'[Average speed] <= xxx
    )
)

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 (
    MAX ( 'Link by Link Emission Rates Table'[Average speed] ),
    FILTER (
        'Link by Link Emission Rates Table',
        'Link by Link Emission Rates Table'[Time of day]
            = MAX ( 'Link by Link Summary'[Time of day] )
    )
)

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.

[rohitj]

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

[rohitj · Accepted solution]

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 (
    MAX ( 'Link by Link Emission Rates Table'[Average speed] ),
    FILTER (
        'Link by Link Emission Rates Table',
        'Link by Link Emission Rates Table'[Average speed]
            <= 'Link by Link Summary'[Average speed]
    )
  )

VAR upperbin =
    CALCULATE (
        MIN ( 'Link by Link Emission Rates Table'[Average speed] ),
        FILTER (
            'Link by Link Emission Rates Table',
            'Link by Link Emission Rates Table'[Average speed]
                >= 'Link by Link Summary'[Average speed]
        )
    )

VAR THCEmissionLower =
    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] 
                =Lowerbin
                && 'Link by Link Emission Rates Table'[Time of day]
                = 'Link by Link Summary'[Time of day]
                && 'Link by Link Emission Rates Table'[Season] = 'Link by Link Summary'[Season]
        )
    )

VAR THCEmissionUpper =
    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] = Upperbin
                && 'Link by Link Emission Rates Table'[Time of day]
                = 'Link by Link Summary'[Time of day]
                && 'Link by Link Emission Rates Table'[Season] = 'Link by Link Summary'[Season]
        )
    )

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

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

RETURN
    THCinterpolated