Jump to content
We've recently updated our Privacy Statement, available here. ×

Filter rows with delimiter-separated values based on a "Contains" logic


RobotZebra

Recommended Posts

Hello,

I have several columns that contain delimiter-separated values (in this case, a tab, but I can change this to a comma or another delimiter) and I would like to allow users to filter based on the presence of any item within the delimiter-separated list. For example, my table looks like this:
 

Column A Column B
Banana, Apple, Orange Bread, Rice, Pasta
Apple, Banana Pasta

A listbox filter would look like this for Column A:

Banana, Apple, Orange
Apple, Banana

The user would have to select both the first and second filter options to display the two rows, but I would like to have them select only "Banana" or "Apple" to show both rows since they both contain those words. I have a document property which is a list that contains all the unique values from the column, but I'm not sure if it's possible to create a filter using this document property. Is there any way to create a filter using the list of unique values?

Link to comment
Share on other sites

This is obviously a simplified version of the actual problem you are trying to solve, but hopefully it won't take too many iterations to get to a solution.

Using this example, I used a case() statement and the find() function to create a new column, which you can use as a filter:

case  when Find(DocumentProperty("your document property"),[ColA])>0 then "Yes" else "No" end as [Found]

So if you select 'Banana' or 'Apple' in the document property then you'll have a new column called 'Found' that will show "Yes" or "No", then you can filter to only show "Yes" values.

Link to comment
Share on other sites

Hi Kirsten, thank you for your quick reply. Your suggestion is a good idea, but the document property can be quite lengthy in the actual visualization (between 15-20 distinct values, potentially more in the future) and I would like for the property to be populated upon dashboard initialization so it's always up to date. Is there a way to populate a drop down or listbox filter, ideally a multiple select with the items in the document property, then use this to filter the data table? Or if you have any other suggestions, that would be appreciated! Thank you.

-Tiffany

Link to comment
Share on other sites

Hi David, thanks for your reply. I'm not sure this would work since it seems the listbox would still display the values as they are listed in the column. For example, row A would have 'Banana, Apple, Orange' and row B would have 'Apple, Banana', so a user cannot see all rows where 'Banana' or 'Apple' exists in the column without selecting the listbox option that shows the exact string in the column. In this simplified scenario, they would have to select both listbox options, which for a large data table with many different combinations of "fruits' could be quite inconvenient. 

Is there any other way of doing a 'Contains' type search where the user can select from a list of distinct values?

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...