Why Filters matter
Databricks AI/BI Dashboards become truly useful when users can slice data by date, segment, status, or region without editing SQL. Filters make this possible by separating what users control in the UI from how queries run in the warehouse.
Design scalable filters in Databricks AI/BI dashboards. Learn filter scope, when to use parameters vs. field filters, and how to build single- and multi-select dropdowns with SQL.
What You'll Learn
- Understand global, page, and widget-level filter scope
- Choose between parameters and field filters based on performance and scale
- Implement single-select, multi-select, and “All” filter patterns
- Build query-driven dropdowns with real SQL examples
This post uses the samples.tpch schema to show how to design filters with various scopes, choose between Filter fields and parameters, and implement single select or “All” and multi-select filters with query-based dropdowns
Some Basics
AI/BI dashboards offer the following types of filters on the dashboards : Multiple-values, Single value, Date picker, date range picker, Text entry and Range slider.
When we configure a filter, we choose which datasets it applies to.
We can have more than one value to filter-by from a dataset using the Multiple values option.
We can also have multiple datasets associated with a filter. When this is done, ensure that the columns are representing the same data values. Selecting Nation from one dataset and Priority from another dataset in the same filter will mix nation and priority values and result in mis-applying the values in one of the two datasets.
Also, ensure that the values of the columns chosen to filter from multiple datasets are the same. For example, if we are filtering on Priority from two datasets A and B, they should both have the same count and data type of values. If dataset A has values { ‘Priority1’, ‘Priority2’, ‘Priority3} as distinct values then dataset B should also have the same - no more and no less.
Filter scope: global, page, and widget
Filters can apply to different parts of a dashboard depending on how you configure them.
Global Filters
Use Global Filters to apply a single set of selections across the entire dashboard (all pages) for visuals that share the same datasets—typical examples include a global date range, region, or segment.
The image above shows Priority as a Global Filter. Any chart or table on any page in the dashboard that includes this column will respect the Priority Selection
- Other Examples: a global Order Date Range filter to control the date range for all charts on the entire dashboard, using two parameters like: param_start_date and: param_end_date
Page-level filters
Use Page-level filters when a control should affect only one page. For example, a detail page needs its own local “Product Category” separate from a summary page.
The image above shows Nation as a Page filter.
- All charts and tables in this page that use the Nation as a Predicate will respect the selections
- Other pages will not be affected by this filter.
- There can be one or more filters on a page to filter the data. For example, there could be a “Segment” filter in addition to a Nation filter
Widget-level (static) filters
Use widget-level (static) filters when the value must be fixed and unchangeable for a given chart (for example, side-by-side charts permanently pinned to different regions). The image above shows s_name (supplier name) as a Widget filter.
(Note that this is hidden when the dashboard is published).
- The underlying dataset shows all suppliers, but only one Supplier is shown in the chart
- Other Examples: a bar chart that shows revenue by o.o_orderstatus with a widget-level filter restricting status IN ('F','O').
When to use Parameters vs Filter fields
Databricks supports both field filters and parameters, which behave differently and have distinct performance characteristics.
Field filters work directly on dataset fields (post-query) in the browser, while parameters inject values into the SQL query itself before execution. Parameters enable filter selection that will re-run the underlying queries with new predicates.
Filter fields
Filter fields operate on fields in the dataset (such as status or segment).
Filter fields do not change SQL query text, and hence they do not re-run the query, unless the result set is over 100K rows or the size exceeds 100MB. Since the filtering happens on an available dataset, it is fast, does not incur additional cost and provides effective performance with predictable values.
Widget-level filters are static. Once published they will display the visualization with those filters. One scenario is when we want to display the same visualizations with different filters. For example - sales revenue by product for different regions.
Use Page-level filters to give the user the ability to change the filter values. If there is more than one chart that is dependent on the filter, ensure that they are tied to the same dataset.
- Best for:
- Simple, local filtering on small to medium result sets.
- Cases where you only need to affect one or a few visuals.
- Consider the sample SQL and output from the Data tab in the Dashboard.
WITH supp_norm AS (
SELECT n.n_name AS nation_name, p.p_name as part_name, s.s_name, ps.ps_supplycost, p.p_retailprice
FROM samples.tpch.supplier s
JOIN samples.tpch.partsupp ps ON s.s_suppkey = ps.ps_suppkey
JOIN samples.tpch.part p ON ps.ps_partkey = p.p_partkey
JOIN samples.tpch.nation n ON s.s_nationkey = n.n_nationkey
)
SELECT nation_name, s_name, part_name, SUM(p_retailprice) AS netprice, SUM(ps_supplycost) AS part_cost
FROM supp_norm
GROUP BY nation_name, s_name, part_name
We use this to build the sample chart below. Note that the part name “chiffon red coral dim” is selected in the widget filter on the right side. Since this widget will not be displayed in the published dashboard, we indicate the selection “Supplier part cost by nation - chiffon red peru coral dim” in the title. If we want to show two charts with different filter values side by side, this method is useful.
The next example shows the part_name filter as a page-level filter. We associate the dataset used in the chart with the filter that we create as a separate widget in the page. The values will be available to the user as a drop down list.
Parameter filters
Parameter filters correspond to query parameters and change the dataset SQL when they change. The parameters in a query are coded using:param_name, where param_name can be any predicate like nation, date, thresholds, etc.
- Best for:
- Large datasets where you need to filter early.
- Multi-select logic inside WHERE or HAVING.
- Thresholds (like minimum revenue) or time-grain switches.
- This example uses a query called Revenue Trends by Nation, and we use a parameter called:nation for the column n_name in the nation table. The parameter details in the Data panel are shown with a selection as ‘ALGERIA’
- The query definition and the parameter definition are shown below. Note that Allow multiple selections is not enabled.
-- Revenue Trends by Nation to be used in Order Analysis page
SELECT
o_orderdate, n_name AS nation, o_orderpriority as Priority,
sum(l_extendedprice * (1 - l_discount) * (((length(n_name))/100) + (year(o_orderdate)-1993)/100)) AS revenue
FROM
`samples`.`tpch`.`customer`, `samples`.`tpch`.`orders`,
`samples`.`tpch`.`lineitem`, `samples`.`tpch`.`nation`
WHERE
c_custkey = o_custkey
AND l_orderkey = o_orderkey
AND c_nationkey = n_nationkey
AND n_name = :nation
GROUP BY o_orderdate, Priority, nation
ORDER BY nation ASC, Priority ASC, o_orderdate ASC;
On the dashboard page, this filter is shown as “Nation - parameter based filter”. This filter uses the Nation, and it applies to the same datasets it will be used on. In this example, the nation field in the Revenue Trend by Nation dataset is associated with the Parameter. It is important to define this as a Parameter and associate the nation column in the dataset. Choosing this as a Field filter will default to using the results in the dataset defined with the default selection of ALGERIA.
The Revenue Trend by Nation chart should use the same dataset - Revenue Trends by Nation.
We can now type a different value for the Nation, besides the default value of ALGERIA. Only one value can be provided as this is a single-value filter.
In the next section, we discuss how we can switch from one Selection to All or Multivalues and show how to build a drop-down list of values to filter on.
|
Scenario |
Filter fields emphasis |
Parameters emphasis |
|
Need quick UX on small datasets |
Browser-side result filtering. |
|
|
Need complex SQL conditions |
Limited to end-of-query filters. |
|
|
Cascading cross-dataset controls |
||
|
Centralized dashboard controls |
Tied mainly to their datasets. |
Deep dive: parameter patterns with “All” and multi-select
Parameters become especially powerful when you design them to support “All” and multi-select behavior.
Single-select “All” pattern
For a single-select parameter, use a sentinel like 'All' and an OR condition in the WHERE clause: This will enable a single value to be passed as a query parameter or the value ‘All Nations’. As long as this value is used consistently in the queries and the widget,display it will work.
To achieve this, we change the predicate from
AND n_name = :nation
TO
AND (:nation = "All" OR n_name = :nation)
Another way to accomplish this is
AND IF (:nation = "All", true, n_name = :nation)
Multi-select with ARRAY_CONTAINS
For multi-select, allow multiple selections in the parameter and use ARRAY_CONTAINS in the predicate. This will help us to select one or many or ‘All Nations’
There are three changes to make for this:
- In the query, change the condition
AND (:nation = "All" OR n_name = :nation)
TO
AND ( ARRAY_CONTAINS(:nation,"All") OR ARRAY_CONTAINS(:nation,n_name))
- Select Allow multiple selections checkbox in the parameter details panel.
- In the Dashboard page, we change the filter widget to Multiple values instead of a Single value. Note that the query dataset is Revenue Trends by Nation for both the Filter and the Chart widgets. The filter widget uses the Parameters field to select the nation filter, while the chart does not have any filters
Building query-based dropdowns
Databricks enables you to populate parameter dropdowns with query results, ensuring that lists stay in sync with the data. This will help in specifying the correct values for the parameters.
Step 1. To define a drop-down list of valid nations, we build a query to extract all the Nations
— Nation_list query to use as a Drop down
SELECT
distinct n_name as nation_name
FROM
samples.tpch.nation
Step 2. We associate this on the Order Analysis page as a field in the Nation_list with Multiple values. We use the nation in the Nation_list as a Filter, which effectively gives us the drop-down list.
The Parameters will still point to the column Nation in the Revenue Trends by Nation
Now we have a drop-down list of values for Nations that allows multiple selections.
Global Filters
To show this we will add a date range filter to the Revenue Trends by Nation dataset query.
AND o_orderdate BETWEEN :order_date.min AND :order_date.max
Edit the parameter type to Date Range.
Use the calendar pop-ups to select a default date range and execute the query.
On the dashboard page, choose the funnel icon for Global Filter, next to the Data tab. Create a filter widget and name it Date Range - Global Filter. Associate the datasets on which this filter needs to operate. In this example, we associate the order_date from the Revenue Trends by Nation dataset as a Parameter.
Click the Date Range and choose different dates, and see the values change.
In a similar way, we can associate more datasets with this Global Filter and by adding them as parameters.
To Try further:
If you’re building your first Databricks AI/BI dashboard, start by adding a single parameterized date range and segment dropdown using the examples. Once that’s working, expand to multi-select status filters and threshold parameters for Top N customers.
Use this query to build a data set called Revenue by Order Priority. Ensure the parameter is defined as a Date Range
SELECT
o.o_orderpriority AS priority,
l.l_shipmode AS ship_mode,
COUNT(*) AS order_count,
o.o_orderdate
FROM samples.tpch.orders AS o
JOIN samples.tpch.lineitem AS l
ON o.o_orderkey = l.l_orderkey
WHERE o.o_orderdate BETWEEN :order_date.min and :order_date.max
GROUP BY o.o_orderpriority, l.l_shipmode, o.o_orderdate
ORDER BY priority, ship_mode;
Add a chart to show Revenue by Order Priority.
Add the Global Filter to this dataset.
Add the Priority Filter as a Page Filter on this page. Associate the Priority column as a filter.
You should see something like this depending on your selections
References:
Databricks Dashboard Parameters and Filters:
- https://docs.databricks.com/aws/en/dashboards/parameters.
- https://docs.databricks.com/aws/en/dashboards/filters
