Filter "is one of" in the Pipeline with a Variable

Data Pipeline

There may be a situation where you need to filter on a dropdown variable list in the data pipeline. Let’s say there is a dropdown variable control that lists all of the states in the United States.

State dropdown

We want to select certain states in the dropdown and have our chart filter on those states. This can be done by dragging the state field into the filters, however we don’t want to do that because we need the initial query not to filter on any state selection. As a result, the filtering needs to be done in the pipeline.

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 the State field in two times when building the chart, or by adding a column in the pipeline.

Assuming you have some pipeline steps before the filter, we will choose the latter for this example. To simply clone a column, use an “Add Column” step and select ‘Custom Formula’, writing in the column name with quotations. We’ll call the new column, “State2”.

Add Column "State2"

The next step is to use a Case Statement. The data pipeline uses SQLite syntax. What we will be doing is forcing a boolean out of one of the State columns.

Add an “Edit Column” step and use this case statement on the “State2” column:

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

Replace “DROPDOWN” with the name of your dropdown filter and “State2” with the name of the column you are editing.

Case statement in Edit Column step
This will connect the dropdown filter to this pipeline step.

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

Filtering rows in Chartio

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

By following these steps, this adds a “is one of” filter in the data pipeline.

Related Data Pipeline Help Articles

See more