Need something like lookup or DAX

[Anonymous]

Hi,

I have two columns, ColumnA and ColumnB having the below records

Actual Data

Column 1	Column 2
Dan	Dan
Roger	Jeff
Eugiline	Max
Samuel	Roger
Peter	Eugiline

I want to compare Above two columns and display result as shown below in powerBi using formula

Result

Jeff

Max

Result

Samuel

Peter

Thanks,

Ravi

[v-huizhn-msft]

Hi @Anonymous,

>>I want to compare Above two columns and display result as shown below in powerBi using formula

How to compare the two columns? I am unable to reproduce based on your given sample data. Maybe some values are missing out in your given example.

 I post the solution based on my understanding for your requriement.

Type the example data table to PowerBI.



Please click Nwer Table under Modeling on home page. type the following formula, you will get the two expected table.

Tab1 = SELECTCOLUMNS(FILTER('Actual Table',OR('Actual Table'[Column 2]="Jeff",'Actual Table'[Column 2]="Max")),"Result",'Actual Table'[Column 2])

Tab2 = SELECTCOLUMNS(FILTER('Actual Table',OR('Actual Table'[Column 1]="Samuel",'Actual Table'[Column 1]="Peter")),"Result",'Actual Table'[Column 1])

If this is not what you want, please share more details for further analysis.

Best Regards,
Angelia

[Anonymous]

Hi Angelia,

I am looking for more of dynamic approach, I have two columns as below, First I have to compare records of column1 with column2 and display the non-matching records(samuel ,peter) in a new column(say column3), similarly comapre records of column2 with column1 and display the non-matching records(Jeff,max) in another column(say column4).

Actual

Column 1	Column 2
Dan	Dan
Roger	Roger
Eugiline	Eugiline
Samuel	Jeff
Peter	Max

Thanks,

Ravi

[v-huizhn-msft · Accepted solution]

Hi @Anonymous,

Please create calculated columns using the following formulas and get expected result shown in screenshot.

Column3 = IF(Test[Column 1]=Test[Column 2],BLANK(),Test[Column 1])

Column4 = IF(Test[Column 1]=Test[Column 2],BLANK(),Test[Column 2])

Best Regards,
Angelia

[Anonymous]

Thanks Angelia, it works:)

Regards,

Ravi

[kcantor]

@Anonymous

Just so we are clear, the end result you are looking for is something like "IF the name from column 2 is found in column 1, then 0 else 1" to create a flag that you could use to bubble up the names that exist in column 2 that do not exist in column 1? That way you could use the flag as a slicer to show names.

Does that sound about right?

Not saying that is a calculation, just asking if that is what you are looking to do.

@v-huizhn-msftdoes that seem to be ther request?

[Anonymous]

Yes thats correct, I wanna comapre the records in column1 and column2, and display the the names that exist in column 2 that do not exist in column 1 and vice versa.

[v-huizhn-msft]

Hi @Anonymous,

If you have resolve your issue, please mark right reply as answer, which will help more people.

Best Regards,
Angelia