Forum Discussion

Moskalyk_Dmytro's avatar
Moskalyk_Dmytro
New Member
2 years ago

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

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!
CONTAINSSTRING
DAX

2 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
    Anonymous
    Not applicable
    2 years ago

    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.