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.

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!

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

6 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    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!