topic Filter rows if they contain at least one value from a text array in DAX Commands and Tips

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

Moskalyk_Dmytro — Fri, 23 Feb 2024 13:58:04 GMT

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!

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

lbendlin — Fri, 23 Feb 2024 20:35:21 GMT

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.

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

Anonymous — Mon, 26 Feb 2024 08:12:17 GMT

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.