Forum Discussion

o-johnralphp's avatar
o-johnralphp
Icon for Advocate I rankAdvocate I
10 months ago
Solved

Look for Values from 2 Columns in a Separate Table

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
InvoiceItemsDesired Output
12345Banana apple grapes orangePackage1,Package2
123456apple orangeNo Match
123457Banana orange applePackage2

 

Table 2
PackageTypeItemAItemB
Package1grapesorange
Package 2bananaorange

 

Many thanks  for the help.

 

 

 

  • rohit1991's avatar
    rohit1991
    10 months ago

    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 )

     

2 Replies

  • rohit1991's avatar
    rohit1991
    Icon for Super User rankSuper User
    10 months ago

    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 )