Documentation Data Pipeline FAQs

Filter "is one of" with a Dropdown in Data Explorer

There may be a situation where you need to filter on a Dropdown variable list in the Data Pipeline. Let’s say you have a Dropdown variable control that lists all of the states in the United States on your dashboard.

State dropdown

We want to select multiple states in the Dropdown and have our chart filter on those states. This can be done by dragging the State field into your Dataset filters. However, there may be cases where you need the filter to be applied to your query results after applying some Pipeline steps. You can then apply a multi-select filter in the Pipeline by following the steps below:

  1. The first step to create your “is one of” Pipeline filter is to make a clone of the column you want to filter on. This can be done either by dragging in the field two times when building your initial Dataset query or by adding a column cloning the field in the Pipeline.

    Assuming you have some Pipeline steps before the filter, we will choose the latter for this example. To quickly clone a column, use an Add Column step and select Custom Formula as the Formula type. Then just write in the column name you’d like to clone in quotations in the Formula field.

    Here, we’re looking to filter using our State column, therefore, we’ll enter “State” as the Formula and name the new column State2.

    Add Column "State2"

  2. The next step is to use a Case Statement. The Data Pipeline uses SQLite syntax which we’ll utilize to set this up. We’ll essentially be forcing a boolean out of one of the State columns to use as a filter.

    To do this, add an Edit Column Pipeline step for your State2 column, select Custom as the Formula Type, and use the Case Statement below in the Formula field:

     CASE WHEN {DROPDOWN.IN('"state2"')} THEN 1 ELSE 0 END

    Replace DROPDOWN in the formula with the name of your Dropdown filter and State2 with the name of the column you’re editing. This will connect the Dropdown filter to this Pipeline step.

    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.

    Filtering rows in Chartio

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

By following these steps, you’ll have added an “is one of” filter in the Data Pipeline so you can 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!