Forum Discussion

Krish89's avatar
Krish89
Helper II
5 years ago

Lookup values based on multiple conditions

Hello,

I have an excel file with two sheets called Data and MF.

DataMF

S.NONew :  =IF(OR(D2="#";D2="NULL");C2;D2)

CombinedValue: = B2&A2&E2 (concatenation of ID ,IDNew, S.NONew)

Rep: = IFERROR(IF(OR(G2="BLANK";G2="CN";G2="PERSON OR COMPANY'S NAME");VLOOKUP(B2;MF!A:B;2;FALSE);VLOOKUP(A2;MF!A:B;2;FALSE));"UNASSIGNED")

 

I want to implement the S.NoNew, CombinedValue and Rep columns in Power BI desktop instead of in Excel.

I imported the data into Power BI and i created S.NoNew and CombinedValue columns but I need support in creating the Rep column in Power BI.

 

Regards,

Krishna.

Need Help

3 Replies

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Community Support
    5 years ago

    Hi Krish89 ,

     

    You can create the following calculated column in Data table:

     

     

    Rep = var a =LOOKUPVALUE(MF[Rep],MF[ID],Data[ID]) return IF(ISBLANK(a),"UNASSIGNED" ,a)

     

     

    Here is the screenshot of my sample data:

     

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai

    • Krish89's avatar
      Krish89
      Helper II
      5 years ago

      Hi v-deddai1-msft ,

       

      Thanks for your help!!

      But here in your calculated column the IF condition is missing(refer my post).

       

      Regards,

      Krishna.

      • v-deddai1-msft's avatar
        v-deddai1-msft
        Community Support
        5 years ago

        Hi Krish89 ,

         

        I am sorry that I am not familiar with Excel. If you can explain more about the logic of Rep column , I will write the right formula for it.

         

        Best Regards,

        Dedmon Dai