Check if column values contain any string from a list of strings - Power BI

Question

I have a data table with a column titled "Tag Id" and a separate list of strings. I need to check each Tag Id value to see if it contains any of the strings from the list, and then calculate the total number of tags for which this is true.

So for example, if the first tag Id value was "101P0", and the list of strings was "P0", "P1", and "P2" (my actual list is about 400 strings), then it should determine that one of the strings in the list (P0) is contained in the tag name, and therefore include it in the total count.

I am currently able to check each Tag Id for one string at a time using the below function (in this case I am checking for P1):

Tag Ids with P1 = 
VAR __FILTERED_VALUE = CALCULATE(
    COUNTROWS(
        'Tag Table'
    ),
    KEEPFILTERS(
        CONTAINSSTRING( 'Tag Table'[tags.Id], "P1")
    )
)

RETURN (
        __FILTERED_VALUE
    )

Here's the issue: As I stated above, my actual list of strings that I need to check for is about 400 strings long. Repeating this function 400 times is completely unrealistic, so I need a way to pass in the list of 400 strings and have it check for every single one all in one function. This list of strings is currently in Column1 of a separate data table named "P Values" ('P Values'[Column1]).

Is there a way I can do this? And what would that function look like? Any help is greatly appreciated!

Answer 1

Try something similar to:

TagTest = 
  var tblTagCount = 
    SUMMARIZE(
      'Tag Table',
      'Tag Table'[tags.Id],
      "tagCount", CALCULATE(
        COUNTROWS('P Values'),
        CONTAINSSTRING(MIN('Tag Table'[tags.Id]), 'P Values'[Column1])
      )
    )
  RETURN COUNTX(tblTagCount, [tagCount])