Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
msantillan
Helper II
Helper II

Compare 3 columns from 1 column in another table

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.

1 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.

View solution in original post

5 REPLIES 5
amitchandak
Super User
Super User

@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

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.

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

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

 

Table 1:

 

ReporterCreatorAssignee(New Column)
BillMarkBillBill
MarkTomTomTom
James MarkJames

 

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

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.