Problem in Comparing Two Columns

[Anonymous]

Hi all,

I'm facing a problem with comparing to columns.

so what basically happens when I create a conditional column is that it compares a row with it corresponding row in the other column.

however, I need to compare a single values from the first column with all values fro the second column until it finds a match, else "New"

I will add an example just to clarify it more.

if "web Developer" ( in 'Actual Jobs') = 'Career Path Jobs' 

then 'Career Path Jobs' 

Other "New" 

But as I said, it only compares a singles row while I want it to iterate on all 'Career Path' Column until it finds a match.

[Anonymous · Accepted solution]

@Anonymous 

Create the following column: 

Column = IF([Actual Jobs] in VALUES('Table'[Career Path Jobs]),[Actual Jobs],"New")

The downside is if the job name is not exactly same in both columns, they cannot be detected. For example, there are 2 spaces in the "Account  Manger" in Column 2, but only 1 space in Column 1. As the result, it returns "New" because they are not matching. You might want to update the list to prevent such differences.

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Try a new column like

if(not(isblank(LOOKUPVALUE([Actual Jobs], [Career Path Jobs],[Actual Jobs]))),"New" ."Career Path Jobs")

or

new column =
var _1 =[Actual Jobs]
return
if( countx(filter(search(_1,[Career Path Jobs],,0)>0),[Career Path Jobs])+0=0,"New" ."Career Path Jobs")

[Anonymous]

@amitchandak  

I tried applying those two solutions but they are comparing Career Pth jobs colomns with itself.

[amitchandak]

@Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

@amitchandak 

Yes, sure!

https://drive.google.com/file/d/1wUp8rbTvrO1qLaVb0BE-bZhRyIC7sq5f/view?usp=sharing

 here is a link to the excel sheet 

mainly the concept is when there is a match between the first and the second coloumn then write the 'Career path job'

else write "New"