DAX help with pattern matching between two non-related Tables

[OrionTL]

Hi,

I'm still new to Power BI, so any help is appreciated.

I have two tables, one with all raw data for who worked on what product like so (TableA):

Product	Person Who Worked on it
ABC-ABBA	Bob
DEF-ABBA	Steve

I have a second table that dictates which of the products are harder to work on (TableB):

Hard Strenous Product

DEF

Is there a way to pattern match products from TableA with known hard streneous products from TableB and create a new column for it on TableA? I'm hoping for an "IF" statement where, if it's a hard product, return "HardProduct" and if it's not, then return "NotAHardProduct" within the cell values on the same rows like below:

Product	Person Who Worked on it	Product Type
ABC-ABBA	Bob	NotAHardProduct
DEF-ABBA	Steve	HardProduct

I know I can do this on M but within "M" I can't refer to the TableB from TableA when creating a custom column within TableA.

[tamerj1 · Accepted solution]

Hi @OrionTL 
Please follow sample file with the solution https://www.dropbox.com/t/wi6ldBAjf4fGEUkX

TableC = 
ADDCOLUMNS (
    TableA,
    "Product Type", 
        IF (
            ISEMPTY (
                FILTER (
                    TableB,
                    CONTAINSSTRING ( TableA[Product], TableB[Hard Strenous Product] )
                )
            ),
            "Not A Hard Product",
            "Hard Product"
        )
)

[OrionTL]

Thank you @tamerj1 !

I was hoping to make it upon the already existing tableA but creating a new one is fine too.

I appreciate your help!

[tamerj1]

@OrionTL 
Ofcourse you can. Actually it is the same code of the column created inside ADDCOLUMNS 

ProductType = 
IF (
    ISEMPTY (
        FILTER (
            TableB,
            CONTAINSSTRING ( TableA[Product], TableB[Hard Strenous Product] )
        )
    ),
    "Not A Hard Product",
    "Hard Product"
)

[tamerj1]

Hi @OrionTL 

that would be much easier using dax calculated table or a measure in your power bi report. Would you consider that?

[OrionTL]

I have tried using a DAX calculatedtable like so

HType = 
VAR Matches =
CALCULATETABLE
(
    GENERATE('HardProducts',
    FILTER
    (
        Product_BuyOff,
        SEARCH
        (
            [Assemblies],
            [Part Number],
            1,
            0
        )
        >0)
    )
)
RETURN
    CONCATENATEX(
        Matches,
        "LH",",")

but I just get blank cells for the non-matching ones and "LH" for the matching ones. I can't figure out how to modify that code to make the blanks return another value when it's not matching.