Multiple Values for Contains Filter

Is there a way to have multiple value for a Contains filter? I can only get a single value to work. The workaround is to add a new column with a contains formula and filter off that. But that adds more steps for each time I need to update the filter.

Hello, Nick, you can do this with a formula, as you suggested, but there is a way to do that once, without having to update the formula every time you need to add a new value to the filter.

Let’s say your PRODUCT table has a field “PRODUCT_TYPE” with values such as ‘apples’, ‘oranges’, ‘plums’, ‘pears’, ‘peaches’ and so on. The goal is to be able to search it by typing in a string like this “ora peac plu”. Here are the steps.

  1. Create a parameter [product type search parm], that a user would type multiple search values into, separated by a space.

  2. Create a formula [Product Type Filter], that would parse/split individual items and tests each of them.
    If (Contains(PRODUCT_TYPE],SplitPart([product type search parm]," “,1)) OR Contains([PRODUCT_TYPE],SplitPart([product type search parm],” “,2)) OR Contains([PRODUCT_TYPE],SplitPart([product type search parm],” “,3)) OR Contains([PRODUCT_TYPE],SplitPart([product type search parm],” ",4)) , [PRODUCT_TYPE], Null)

  3. Make a filter from this formula, and set it to NOT include nulls

The above formula supports up to 4 search values, separated by a space, but you can modify it to support a larger number of search values. The search here is case sensitive, if you want to make it case insensitive, just add Upper(), like this:

  • If (Contains(Upper(PRODUCT_TYPE]),SplitPart(Upper([product type search parm])," “,1)) OR Contains(Upper([PRODUCT_TYPE]),SplitPart(Upper([product type search parm]),” “,2)) OR Contains(Upper([PRODUCT_TYPE]),SplitPart(Upper([product type search parm]),” “,3)) OR Contains(Upper([PRODUCT_TYPE]),SplitPart(Upper([product type search parm]),” ",4)) , [PRODUCT_TYPE], Null)

Hope this helps!

1 Like

Hi Yuri,

Can I use the following formula for [Product Type Filter]? There is no limitation on the number of values.

  • If (Contains([product type search parm], [PRODUCT_TYPE]), [PRODUCT_TYPE], Null)

Thanks,

Hello, Hugo, sorry for the delay, our office was closed for the Thanksgiving holiday.

You can use that formula for filtering, if you set the filter to exclude nulls.
Or, this shorter version will have the same effect:

Contains([product type search parm], [PRODUCT_TYPE])

As an experiment, I just tried to select 150 values from a multi-select list, each over 100 characters long (that is a 15k csv), and it worked fine. And Snowflake has a 1MB text limit for SQL statements, so you should have plenty of room to maneuver.

Regards!

1 Like