Filter rows if they contain at least one value from a text array

[Moskalyk_Dmytro]

I'm trying to show total sales for a products, witch name string contains at least one of the keywords from a static table. Here is the formula for it:

Sales by Group, EUR =
CALCULATE (
   [Sales, EUR],
   FILTER (
      'Sales Product Lookup',
      CONTAINSSTRING(
         'Sales Product Lookup'[Product Name],
         MAX('Table Keywords'[Keyword])
)))

For now it's pretty useless and only works for visuals in the form of a table where one of the columns = Table Keywords'[Keyword].

How can I force the CONTAINSSTRING function to accept array/column instead of single value? 

Will be realy appreciate for the help!

[lbendlin]

Not possible.  You could frankenstein it by running the CONTAINSSTRING against a composite key (a concatenation of all text columns) but that would be static-y too as you'd have to know the column list in advance.

[Anonymous]

Hi @Moskalyk_Dmytro ,

It seems you are trying to create a calculated column that filters sales data based on keywords from a static table. I think you can make some changes.

First, create a new calculated column.

Keyword Match = 
VAR ProductName = 'Sales Product Lookup'[Product Name]
VAR Keywords = 'Table Keywords'[Keyword]
RETURN
CONTAINSSTRING(ProductName, Keywords)

Now that you have the “Keyword Match” column, you can use it in your visualizations or calculations. For example, if you want to show total sales for products containing any of the keywords, create a measure like this.

Sales by Group, EUR = 
CALCULATE (
   [Sales, EUR],
   'Sales Product Lookup'[Keyword Match] = TRUE()
)

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.