Filter a table based on string values in another table

[Anonymous]

Hi everybody,

Suppose I have the following table, "Table 1", that has a Serial Code column containing a string of text. I am trying to filter this table based on whether or not the Serial Code contains particular substrings. The substrings I want to check for are listed in another table, "Table 2".

Table 1
Row	Serial Code
1	ABC***********
2	*****************
3	***DEF********
4	*******XYZ****
5	***ABC***DEF***
6	****************
7	****************
8	XYZ***ABC*********
9	******************
10	DEF*******XYZ*****

Table 2

Substrings

ABC

DEF

XYZ

Basically the logic is: For each row in Table 1, check if the Serial Code contains any of the Substrings listed in Table 2. If yes, keep the rows. If no, filter out the rows. So the resulting table will be as below.

Result
Row	Serial Code
1	ABC***********
3	***DEF********
4	*******XYZ****
5	***ABC***DEF***
8	XYZ***ABC*********
10	DEF*******XYZ*****

Is this possible with DAX?

Thank you for reading!

[amitchandak · Accepted solution]

@Anonymous , You can try a new column like

New column =
var _1 =countx( filter(Table2, search(Table2[Substrings], Table[Serial Code],,0)> 0),Table2[Substrings])
return
if(not(isblank(_1)),1, blank())

[Anonymous]

Thank you very much, @amitchandak. This was a helpful suggestion!