Multi-select dropdown parameter doesn't allow CONTAINS filter

Question & Answer
1

Hello,

I have a use case where I have enabled control for a multi select dropdown for two different parameters which filter the data in my visuals.
Along with this for the corresponding visual I have filter applied (Include filter) which is based on a calculated field say “KPISUB”. This calculated field contains functions - ifelse, contains. But once this filter is enabled, I keep getting an error with the two multiselect parameter controls saying -

Following items are incompatible with Multi-select drop down. Please update/remove them to fix the broken visuals.

Filter - “CONTAINS” filter on column KPI_SUB

I removed the contains function in the calculated field KPI_SUB and used left function to just compare and check if the left of the string contains the sub string. But still this error is not disappearing. Can you please suggest an alternative?

2

The error message you’re encountering indicates that the multi-select dropdown controls you’ve enabled are not compatible with the “CONTAINS” filter applied on the column KPI_SUB in your visuals. It seems that the filter is causing conflicts with the multi-select functionality.

To resolve this issue, you can consider an alternative approach for filtering based on the KPI_SUB column. Instead of using a “CONTAINS” filter, you can try using a different filter type that is compatible with multi-select dropdown controls.

One possible alternative is to use a “IN” filter. Instead of filtering based on partial string matches, you would filter based on exact matches. Here’s an example of how you can modify your calculated field and filter expression:

  1. Modify your calculated field, KPI_SUB, to generate a list of values or categories. Instead of using the “contains” function, you can use a combination of logical conditions (e.g., “IF” or “CASE” statements) to assign values to specific categories.

    For example, instead of:

    KPI_SUB = IFELSE(CONTAINS([column], "substring"), "Category 1", "Category 2")

    You can use:

    KPI_SUB = IF([column] LIKE "substring%", "Category 1",
            IF([column] LIKE "another substring%", "Category 2", "Category 3"))

    Adjust the conditions and categories according to your specific requirements.

  2. Apply an “IN” filter on the KPI_SUB column in your visual. In the filter settings, you can select the “Include” option and choose the desired categories or values from the multi-select dropdown controls.

    For example, instead of:

    Filter: CONTAINS(KPI_SUB, "Category 1")

    You can use:

    Filter: KPI_SUB IN ("Category 1", "Category 2")

    Adjust the categories or values based on the options available in your KPI_SUB calculated field.

By using the “IN” filter with exact matches and modifying your calculated field to generate specific categories, you can maintain compatibility between the multi-select dropdown controls and the filter applied on the KPI_SUB column. This should help resolve the error and allow you to filter your visuals based on the selected categories or values.

1 Like
3

@tarushtg Thank you for taking time to explain.

Just wanted to check if LIKE is a keyword that can be used in Quicksight calculated functions? I am unable to find any such keyword.

4

HI @tpanati

Have you tried using the locate function in QuickSight as a substitute for like?

For example you could use: ifelse(locate(expression, substring) >0, then-expression-1)

5

Hi @Max @tarushtg
I am facing a similar issue even though I am using ‘in’ function. I have a calculated filed called ‘VAL LIST’ and then used a filter. The ‘in’ function is combined with a multivalue parameter wrapped in ifelse function.
ifelse(in({ID},${IDI}),VAL,'NA')

When I use the calculated field as a filter I get the error message on the ‘ID’ control.

Following items are incompatible with Multi-select drop down. Please update/remove them to fix the broken visuals.

Filter - “CONTAINS” filter on column VAL LIST

Interestingly enough the filters work as expected. Would you know why this happens?