Lookup values based on multiple conditions

[Krish89]

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.

[v-deddai1-msft]

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]

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]

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