cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Krish89
Helper II
Helper II

Get lookup values based on multiple conditions

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

 

Data.PNGMF.PNG

 

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 it would be great if someone can help in creating the Rep column in Power BI.

 

Regards,

Krishna.

3 REPLIES 3
DataInsights
Super User II
Super User II

@Krish89,

 

Try this calculated column:

 

Rep =
VAR vResult =
    IF (
        Data[Name] IN { "BLANK", "CN", "PERSON OR COMPANY'S NAME" },
        LOOKUPVALUE ( MF[Rep], MF[ID], Data[IDNew] ),
        LOOKUPVALUE ( MF[Rep], MF[ID], Data[ID] )
    )
RETURN
    IF ( ISBLANK ( vResult ), "UNASSIGNED", vResult )

 

Hi @DataInsights ,

 

Thank you very much for your help!!

I tried the calculated column but I got the below error because in my MF table the ID column datatype is whole number where as in Data table all the columns are Text. So Lookup function is not working with this datatype mismatch. 

But if i change the data type of ID column in MF table to Text then the DAX query works perfectly fine.

Is it possible to convert the datatpye of ID column in MF table to Text when writing the dax code instead of changing it in Power query.

 

lookup.PNG

 

Thanks in Advance!!

 

Regards,

Krishna.

@Krish89,

 

It's possible, but not recommended. Data types should be consistent throughout the data model. For example, the column Order Date should be a date data type throughout the data model. If Order Date is a date data type in some tables, and a text data type in other tables, you will encounter problems. I highly recommend having the same data type in both tables for the ID column.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.