Documentation Controls and Variables FAQs

Filter with multi-select Dropdowns in the Pipeline

Sometimes, you may need to use a multi-select Dropdown to filter your data by connecting it in the Pipeline rather than in your Query. Connecting multi-select Dropdowns in a Query is pretty straightforward—you’d use the “is one of” operator when setting up your filter; however, you can’t use the “is one of” operator to connect a multi-select Dropdown in the Pipeline. You can still connect it in the Pipeline, but it requires a bit of a workaround.

Let’s say we have a Dropdown that lists all of the states in the United States on your dashboard and has the following settings:

  • Multi-select is selected
  • Empty state is Show All
  • Initial value(s) is deselected

We want to select multiple states in the Dropdown and have our chart filter on those states. Again, we could connect it in our query by adding the State column to the Filters section and using the “is one of” operator, but let’s say we don’t want to do that because we need the initial query not to filter on any state selections. Therefore, the filtering must be done in the Pipeline by following the steps below:

In the Visual SQL interface

  1. The first step to connect your multi-select Dropdown in the Pipeline is to make a clone of the column you want to filter on. You can do this in two different ways:
    • Adding the column twice when building your initial Query or
    • Using a Formula Column to clone the column to the result set.

    Assuming you have some Actions before the filter, we’ll use the latter method for this example. To quickly clone a column, add a Formula Column, select Custom as the formula type, then write in the column name you’d like to clone in double quotes (") in the formula field.

    Here, we’re looking to filter using our State column; therefore, we enter "State" as the formula. We also rename this new column to State2 (the column name is arbitrary because we’ll hide this column at the end).

  2. The next step is to use a CASE Statement. The Pipeline uses SQLite syntax, so that’s what we use to set this up. We’ll essentially force a boolean out of the clone State column to use as a filter.

    To do this, use Apply Formula on the State2 column, select Custom as the Formula Type, and use the CASE statement below in the formula field:

     case when {DROPDOWN_NAME.IN('"State2"')} then 1 else 0 end
    

    Replace DROPDOWN_NAME in the formula with the name of your Dropdown Control and State2 with the name of the column you’re editing. This will connect the Dropdown to this Action.

  3. Next, add a Filter Action to correctly filter on the Dropdown selections. Select to include rows matching all conditions where the column “State2” equals 1 ("State2" = 1).

    Note: If you’re trying to implement an “is not one of” filter, you’d just need to change include to exclude.

    Filtering rows in Chartio

  4. Lastly, hide the boolean “State2” column.

If you followed these steps correctly, you should now have an “is one of” filter in the Pipeline and you should be able to filter your chart using multiple selections from the State Dropdown.

This process also works for multi-select Text Inputs. Go ahead and try it out!


In the Data Explorer interface

  1. The first step to connect your multi-select Dropdown in the Pipeline is to make a clone of the column you want to filter on. You can do this in two different ways:
    • Adding the column twice when building your initial Dataset query or
    • Using an Add Column step to clone the column to the result set.

    Assuming you have some Pipeline steps before the filter, we’ll use the latter method for this example. To quickly clone a column, add an Add Column step, select Custom as the formula type, then write in the column name you’d like to clone in double quotes (") in the Formula field.

    Here, we’re looking to filter using our State column; therefore, we enter "State" as the Formula and name the new column State2 (the column name is arbitrary because we’ll hide this column at the end).

    Add "State2" column using Add Column

  2. The next step is to use a CASE Statement. The Pipeline uses SQLite syntax, so that’s what we use to set this up. We’ll essentially force a boolean out of the clone State column to use as a filter.

    To do this, add an Edit Column Pipeline step to modify the State2 column, select Custom as the Formula Type, and use the CASE statement below in the formula field:

     case when {DROPDOWN_NAME.IN('"State2"')} then 1 else 0 end
    

    Replace DROPDOWN_NAME in the formula with the name of your Dropdown Control and State2 with the name of the column you’re editing. This will connect the Dropdown to this Action.

    CASE statement in Edit Column step

  3. Next, add a Filter Rows step to correctly filter on the Dropdown selections. Select INCLUDE rows and matching ALL conditions from the dropdown where the column “State2” equals 1.

    Note: If you’re trying to implement an “is not one of” filter, you’d just need to change include to exclude.

    Filtering rows in Chartio

  4. Lastly, add a Hide Columns step to hide the boolean “State2” column.

If you followed these steps correctly, you should now have an “is one of” filter in the Pipeline and you should be able to filter your chart using multiple selections from the State Dropdown.

This process also works for multi-select Text Inputs. Go ahead and try it out!