mapping 2 tables with 2 fields

[jppuam]

Hello,

i'm need to filter a table based in one column from a table, but i've to check if that field exists in tw0 columns from the other table.

for example i've a code in table A, and i want to check if that code apears on field 2 ou field 3 from other table.

how can i do that ?

thank you,

JR

[v-lili6-msft · Accepted solution]

hi,@jppuam

    After my research, you can do these as below:

Assume that we have to table like these:

table 1:

table2;

Step1:

use LOOKUPVALUE() function  to search match value

Look Column 2 = LOOKUPVALUE(Table1[Column1],Table1[Column1],Table2[Column2])

Look Column 3 = LOOKUPVALUE(Table1[Column1],Table1[Column1],Table2[Column3])

Step2:

Use this formula to create a table for this match values

Table = DISTINCT(UNION(FILTER(VALUES(Table2[Look Column 2]),Table2[Look Column 2]<>BLANK()),FILTER(VALUES(Table2[Look Column 3]),Table2[Look Column 3]<>BLANK())))

Result:

 

If not your case, 

please share some data sample and expected output. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

Best Regards,

Lin

[edhans]

You might look at using the LOOKUPVALUE() function as a calculated column. It operates similarly to a VLOOKUP() in Excel. If the data is in a different table, the source and destination tables have to have a relationship.

[jppuam]

Hello,

what i'm trying to do is to check if a field 1 is equal to reference or equal to object (these 2 fields in other table).

can i create a new table with the result of the matches ?

thanks,

JR

[jppuam]

How can i put in the filter area one field (removing the null values) or another field ?

i want to put :

filter dep1 (all removing the nulls) or dep2 when equals to something ?

thanks

JR

[edhans]

You are going to need to post some sample data to more clearly explain to us what you are doing.