When you select the Show unfiltered chart data setting for a multi-select “Dropdown” control, this means connected charts will ignore the filter if values in the “Dropdown” control are not selected. Sometimes, you may want your chart to initially filter with all values in the “Dropdown” control. You can manually select each value, but this can be cumbersome, especially if you have a long list of values. Instead, you can follow these instructions for whichever mode you’re using for your query.

For this example, we have a “Dropdown” control with three issue priority values: Low, Lowest, and Medium. We select “Show unfiltered chart data” for when no values are selected and deselect Initial values. Our “Priority” column actually contains five distinct values, so when we don’t have any values selected in our“Dropdown” control, we see all five priorities in our results.

But what we want is to only see the three values shown in our “Dropdown” control when there are no values selected yet.

There are two different approaches you can take: using multiple Visual SQL steps or using only SQL mode queries.

Use multiple Visual SQL steps

1. In order to use this approach, you must include the column to be filtered in your result set, so modify the appropriate query in your chart to add the column then select Run query. Don’t worry—you can hide the column at the end if you don’t need it in your final result set.

In our example, we make sure to add the “Priority” column to our query.

Note: You still need to connect the “Dropdown” control in your query to ensure your chart will still update when values from the “Dropdown” control are selected.

Here’s how the query looks for our example:

In Visual mode - 

In SQL mode - 

2. If you haven’t already, select the query that’s connected to your “Dropdown” control to focus on it. New steps are added below the currently selected Visual SQL step.

3. Use a "Reorder columns" step to move the column used for filtering to the leftmost column of your result set.

4. Create another query that’s identical to the query used to create your “Dropdown” control then select Run query.

5. Edit the “Join” step to use an Inner join then select Save. Now the results of the original query are filtered to only show rows relevant to the values in your “Dropdown” control!

6. Reorder the filtered column back to its original position in your final result set—or hide it if it’s no longer necessary.

The SQL mode approach

The previously mentioned approach may not be what you’re looking for if your query results would initially hit our maximum row limits. Instead, you can edit the SQL mode query itself. You can also opt for this approach if your chart’s query was made with SQL mode and you want to implement the solution in a single step!

In the WHERE clause of your chart’s query, you’ll need to add two conditions—the general format looks like this:

WHERE "table_name"."column_name" IN (DROPDOWN_SQL) AND {DROPDOWN_NAME.IN('"table_name"."column_name"')}

1. For the first condition, "column_name" IN (DROPDOWN_SQL), you need to copy the SQL query of your “Dropdown” control and insert it into the WHERE clause of your chart’s SQL query. In other words, replace DROPDOWN_SQL with your “Dropdown” control’s actual SQL.

2. The second condition, {DROPDOWN_NAME.IN('"table_name"."column_name"')}, ensures that your chart is connected to your “Dropdown” control and applies the correct filter when values are actually selected.

SQL mode approach applied to our example

“Dropdown” control’s SQL query:

SELECT `Jira Issue`.`priority` AS `Priority`
FROM `jira_issue` AS `Jira Issue`
WHERE `Jira Issue`.`priority` NOT IN ('P0','P1')
GROUP BY `Jira Issue`.`priority`
ORDER BY `Jira Issue`.`priority` ASC;

Chart’s original SQL query:

SELECT DATE_FORMAT(`Jira Issue`.`created_at`, 'yyyy-MM') AS `Month of Created At`,
       `Jira Issue`.`priority` AS `Priority`,
       COUNT(DISTINCT `Jira Issue`.`issue_id`) AS `Number of issues`
FROM `jira_issue` AS `Jira Issue`
WHERE {PRIORITY.IN('`Jira Issue`.`priority`')}
GROUP BY DATE_FORMAT(`Jira Issue`.`created_at`, 'yyyy-MM'),
         `Jira Issue`.`priority`
ORDER BY DATE_FORMAT(`Jira Issue`.`created_at`, 'yyyy-MM') ASC, `Jira Issue`.`priority` ASC
LIMIT 100000;	  

Chart’s SQL query after inserting the “Dropdown” control’s SQL query:

SELECT DATE_FORMAT(`Jira Issue`.`created_at`, 'yyyy-MM') AS `Month of Created At`,
       `Jira Issue`.`priority` AS `Priority`,
       COUNT(DISTINCT `Jira Issue`.`issue_id`) AS `Number of issues`
FROM `l00816d97_2bcb_41f1_becc_cce986c234ef`.`jira_issue` AS `Jira Issue`
WHERE `Jira Issue`.`priority` IN (SELECT `Jira Issue`.`priority` AS `Priority`
FROM `jira_issue` AS `Jira Issue`
WHERE `Jira Issue`.`priority` NOT IN ('P0','P1'))
AND {PRIORITY.IN('`Jira Issue`.`priority`')}
GROUP BY DATE_FORMAT(`Jira Issue`.`created_at`, 'yyyy-MM'),
         `Jira Issue`.`priority`
ORDER BY DATE_FORMAT(`Jira Issue`.`created_at`, 'yyyy-MM') ASC, `Jira Issue`.`priority` ASC
LIMIT 100000;