Look for Values from 2 Columns in a Separate Table

[o-johnralphp]

Hi Everyone,

I have 2 separate table (Table1 and Table2) and would like to create a calculated column on the Table1 to return the value based on Table2 2columns.

Table1
Invoice	Items	Desired Output
12345	Banana apple grapes orange	Package1,Package2
123456	apple orange	No Match
123457	Banana orange apple	Package2

Table 2
PackageType	ItemA	ItemB
Package1	grapes	orange
Package 2	banana	orange

Many thanks  for the help.

[rohit1991 · Accepted solution]

Hi @o-johnralphp 

You can do this using a calculated column in Table1 that checks both ItemA and ItemB from Table2 and returns all matching package names.

Calculated column (Table1):

Desired Output =
VAR txt = LOWER('Table1'[Items])
VAR match =
   ADDCOLUMNS (
       'Table2',
      "@pkg",
       IF (
          CONTAINSSTRING ( txt, LOWER('Table2'[ItemA]) ) &&
           CONTAINSSTRING ( txt, LOWER('Table2'[ItemB]) ),
          'Table2'[PackageType]
       )
   )
VAR result =
   CONCATENATEX ( FILTER ( match, [@pkg] <> BLANK() ), [@pkg], ", " )
RETURN IF ( result = BLANK(), "No Match", result )

[o-johnralphp]

Thank you very much!!!