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
v-xjiin-msft's avatar
v-xjiin-msft
Icon for Solution Sage rankSolution Sage
8 years ago

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'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