Forum Discussion

rohitj's avatar
rohitj
Frequent Visitor
8 years ago
Solved

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 e...
Need Help
Show and Tell
Tips and Tricks
  • rohitj's avatar
    rohitj
    8 years ago

    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
rohitj's avatar
rohitj
Frequent Visitor
8 years ago

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's avatar
rohitj
Frequent Visitor
8 years ago

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