Compare 3 columns from 1 column in another table

[msantillan]

Hi, is there a way to add a new column into Table A:

if values from 3 columns in Table A exists in 1 column in Table B,

and the result would be the value from the 1 column in Table B

Something like

if ( contains (Table B, Column 1.B, Column 1.A and Column 2.A and Column 3.A), "Column 1.B", null))

but above code doesn't work.

Thank you.

[amitchandak]

@msantillan , you need to try like

Countx(filter(Table2, CONTAINSSTRING(Table2[Col],Table1[Col1])  && CONTAINSSTRING(Table2[Col1],Table1[Col1]) ),Table2[Col1])

Bring results from Table 2 to table1.

This will a new column in table 1. any count means you condition matched.

https://docs.microsoft.com/en-us/dax/containsstring-function-dax

[msantillan]

Hi @amitchandak I don't particularly get the solution. I'm trying to see if the three columns from Table 1 exists in the 1 column in Table 2. I then get the value from the 1 column in Table 2 into a new column in Table 1.

[amitchandak]

@msantillan ,Can you share sample data and sample output in table format?

[msantillan]

Hi @amitchandak I can't send a sample data. But here is the sample output in table format:

Table 1:

Reporter	Creator	Assignee	(New Column)
Bill	Mark	Bill	Bill
Mark	Tom	Tom	Tom
James		Mark	James

Table 2:

Resource Config

Bill

Tom

James

So, basically Table 1 will check if the names from Table 2 exists in either Reporter, Creator and Assignee. If it exists, the values from Table 2 will be extracted into the new column in Table 1

[msantillan · Accepted solution]

I was too concentrated with using contain and lookup, that I forgot the the use of Filter. Here is the solution I was able to come up with, if anyone else has a more simple answer, please feel free to show it:

New Column = CALCULATE(FIRSTNONBLANK('Table 2'[Resource Config],1),

FILTER('Table 2','Table 2'[Resource Config]='Table 1'[Creator]||

'Table 2'[Resource Config]='Table 1'[Assignee]||

'Table 2'[Resource Config]='Table 1[Reporter]))

Thanks.