topic Re: DAX help with pattern matching between two non-related Tables in DAX Commands and Tips

DAX help with pattern matching between two non-related Tables

OrionTL — Sat, 30 Jul 2022 18:11:56 GMT

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.

Re: DAX help with pattern matching between two non-related Tables

tamerj1 — Sat, 30 Jul 2022 18:59:10 GMT

Hi OrionTL

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

Re: DAX help with pattern matching between two non-related Tables

OrionTL — Sat, 30 Jul 2022 19:05:50 GMT

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.

Re: DAX help with pattern matching between two non-related Tables

tamerj1 — Sat, 30 Jul 2022 19:17:41 GMT

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" ) )

Re: DAX help with pattern matching between two non-related Tables

OrionTL — Sat, 30 Jul 2022 19:24:58 GMT

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!

Re: DAX help with pattern matching between two non-related Tables

tamerj1 — Sat, 30 Jul 2022 19:29:00 GMT

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" )