cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
rohitj Frequent Visitor
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. 

 

 Emission rate table.PNGLink by Link emission rate tableSummary table.PNGLink 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] )

 

1 ACCEPTED SOLUTION

Accepted Solutions
rohitj Frequent Visitor
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 (
    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
3 REPLIES 3
v-xjiin-msft Super Contributor
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 (
    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 Frequent Visitor
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

rohitj Frequent Visitor
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 (
    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

Helpful resources

Announcements
Community Highlights

Community Highlights

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

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

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.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 96 members 1,548 guests
Please welcome our newest community members: