Looking up a measure value in another table

[Anonymous]

Hi All,

I've been tasked with recreating an excel sheet in Power BI.
One of the formulas used in the excel is a nested vlookup, =VLOOKUP(VLOOKUP((('P50'!H13/('Month Hours'!$B$8))/8),RWS!$A:$B,1,TRUE),RWS!$A:$B,2,FALSE)

In Power BI the P50 is a measure, Month Hours is a column in my Calendar table and RWS A:B is now the columns Power KW and Windspeed KMS in a seperate table. 

I want to take the value returned from the first part of the VLOOKUP so (P50/month hours)/8 and look that up aaginst the PowerKW column and then return the windspeed KMS column. I'm not sure it's possible to do in Power BI.

Thanks in advance,

Mike

[Kedar_Pande]

@Anonymous 

Create a measures

Intermediate Value = 
DIVIDE(
DIVIDE([P50], MAX('Calendar'[Month Hours])),
8
)

Closest Power KW = 
MAXX(
TOPN(
1, 
FILTER(
'RWS',
'RWS'[Power KW] <= [Intermediate Value] 
),
'RWS'[Power KW], 
DESC
),
'RWS'[Power KW]
)

Resulting Windspeed KMS = 
MAXX(
FILTER(
'RWS',
'RWS'[Power KW] = [Closest Power KW]
),
'RWS'[Windspeed KMS]
)

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Bibiano_Geraldo]

Hi @Anonymous ,

First, Create a measure to calculate (P50 / Month Hours) / 8

First Lookup Value = 
DIVIDE(
    DIVIDE([P50], MAX('Calendar'[Month Hours])),
    8
)

Create a measure to find the closest value in the PowerKW column:

Closest PowerKW = 
VAR TargetValue = [First Lookup Value]
RETURN
    CALCULATE(
        MAX('RWS'[PowerKW]),
        FILTER(
            'RWS',
            'RWS'[PowerKW] <= TargetValue
        )
    )

Create another measure to return the corresponding Windspeed KMS

Result Windspeed = 
VAR ClosestPower = [Closest PowerKW]
RETURN
    CALCULATE(
        MAX('RWS'[Windspeed KMS]),
        'RWS'[PowerKW] = ClosestPower
    )

Now, you can use the Result Windspeed measure in your visuals to display the calculated windspeed for each context.

[Anonymous · Accepted solution]

hi @Anonymous ,

try below measure
IntermediateValue = DIVIDE([P50 Measure], MAX('Calendar'[Month Hours])) / 8

ClosestPowerKW =
VAR TargetValue = [IntermediateValue]
RETURN
MINX(
TOPN(
1,
FILTER(
'RWS',
ABS('RWS'[Power KW] - TargetValue) =
MINX(
FILTER('RWS', ABS('RWS'[Power KW] - TargetValue) >= 0),
ABS('RWS'[Power KW] - TargetValue)
)
),
ABS('RWS'[Power KW] - TargetValue),
ASC
),
'RWS'[Power KW]
)

ResultWindspeedKMS =
VAR ClosestKW = [ClosestPowerKW]
RETURN
MAXX(
FILTER('RWS', 'RWS'[Power KW] = ClosestKW),
'RWS'[Windspeed KMS]
)