Lookup value

[Allan_Zeng]

Hello Team,

Can someone help to support this issue?

I have 2 tables as below: 

Table "Factor List":

Table "Individual" :

The column "Factor" in red is the result that I wanted.

The result column is base on Department, Role and Target% to find the data range in Table "Factor", 

Then capture the lower level value if didn't meet Target%.

Is this possible in Power BI?

Thank you in advance!

[ryan_mayu · Accepted solution]

@Allan_Zeng 

you can create a column

Column = 
var _target=maxx(FILTER('Factor List','Factor List'[Department]='Individual'[Department]&&'Factor List'[Role]=Individual[Role]&&'Factor List'[Target%]<='Individual'[Target%]),'Factor List'[Target%])
return maxx(FILTER('Factor List','Factor List'[Department]=Individual[Department]&&'Factor List'[Role]=Individual[Role]&&'Factor List'[Target%]=_target),'Factor List'[Factor])

pls see the attachment below

[Allan_Zeng]

Hello Ryan,

Thanks a lot for your solution!

[ryan_mayu]

you are welcome

[tamerj1]

Hi @Allan_Zeng 
Please use

Factor =
VAR CurrentDepartment = Individual[Department]
VAR CurrentRole = Individual[Role]
VAR CurrentTarget = Individual[Target%]
VAR FactorList =
    FILTER (
        'Factor List',
        'Factor List'[Department] = CurrentDepartment
            && 'Factor List'[Role] = CurrentRole
    )
VAR T1 =
    ADDCOLUMNS (
        FactorList,
        "@Difference", ABS ( 'Factor List'[Target%] - CurrentTarget )
    )
VAR T2 =
    TOPN ( 1, T1, [@Difference], ASC )
RETURN
    MAXX ( T2, [Factor] )

[Allan_Zeng]

Hllo  @tamerj1 ，

Thank you so much for your promopt reply!

While I find there is something wrong as below.

Would you please help to double check?

Thank you!

[Allan_Zeng]

Hi @tamerj1 ，

Thank you so much for your promopt reply!

While I find there is something wrong as below.

Would you please help to check?

Thank you!