Forum Discussion

Allan_Zeng's avatar
Allan_Zeng
Frequent Visitor
3 years ago
Solved

Lookup value

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. T...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    3 years ago

    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

tamerj1's avatar
tamerj1
Community Champion
3 years ago

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's avatar
    Allan_Zeng
    Frequent Visitor
    3 years ago

    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!

     

  • Allan_Zeng's avatar
    Allan_Zeng
    Frequent Visitor
    3 years ago

    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!