article Design Filters and Parameters That Scale in Databricks AI/BI Dashboards in Technical Blog

article Design Filters and Parameters That Scale in Databricks AI/BI Dashboards in Technical Blog https://community.databricks.com/t5/technical-blog/design-filters-and-parameters-that-scale-in-databricks-ai-bi/ba-p/148764 <H2><STRONG>Why Filters matter </STRONG></H2> <P><SPAN>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. </SPAN></P> <P><SPAN>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.</SPAN></P> <H2><STRONG>What You'll Learn</STRONG></H2> <UL> <LI style="font-weight: 400;" aria-level="1"><SPAN>Understand global, page, and widget-level filter scope</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>Choose between parameters and field filters based on performance and scale </SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>Implement single-select, multi-select, and “All” filter patterns </SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>Build query-driven dropdowns with real SQL examples </SPAN></LI> </UL> <P><SPAN>This post uses the </SPAN><SPAN>samples.tpch</SPAN><SPAN> schema to show how to design filters with various scopes, choose between </SPAN><SPAN>Filter fields</SPAN><SPAN> and </SPAN><SPAN>parameters</SPAN><SPAN>, and implement </SPAN><SPAN>single select</SPAN><SPAN> or “</SPAN><SPAN>All</SPAN><SPAN>” and </SPAN><SPAN>multi-select </SPAN><SPAN>filters with query-based dropdowns </SPAN></P> <H3><SPAN>Some Basics</SPAN></H3> <P><SPAN>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.</SPAN></P> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="User16844985548_0-1771461822164.png" style="width: 400px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/24138i98644F2731F244BD/image-size/medium?v=v2&px=400" role="button" title="User16844985548_0-1771461822164.png" alt="User16844985548_0-1771461822164.png" /></span></P> <P> </P> <P> </P> <P><SPAN>When we configure a filter, we choose which datasets it applies to.  </SPAN></P> <P><SPAN>We can have more than one value to filter-by from a dataset using the </SPAN><STRONG>Multiple values</STRONG><SPAN> option.</SPAN></P> <P><SPAN>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 </SPAN><I><SPAN>Nation</SPAN></I><SPAN> from one dataset and </SPAN><I><SPAN>Priority</SPAN></I><SPAN> 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. </SPAN></P> <P><SPAN>Also, ensure that the values of the columns chosen to filter from multiple datasets are the same. For example, if we are filtering on </SPAN><I><SPAN>Priority</SPAN></I><SPAN> 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. </SPAN></P> <H2><STRONG>Filter scope: global, page, and widget</STRONG></H2> <P><SPAN>Filters can apply to different parts of a dashboard depending on how you configure them. </SPAN></P> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="User16844985548_1-1771461822165.png" style="width: 400px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/24140i433FE7D9F2FFCA90/image-size/medium?v=v2&px=400" role="button" title="User16844985548_1-1771461822165.png" alt="User16844985548_1-1771461822165.png" /></span></P> <P> </P> <P> </P> <H3><SPAN>Global Filters</SPAN></H3> <P><I><SPAN>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.  </SPAN></I></P> <P><SPAN>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</SPAN></P> <UL> <LI style="font-weight: 400;" aria-level="1"><SPAN>Other Examples: a global Order Date Range filter to control the date range for all charts on the entire dashboard, using two parameters like:</SPAN><SPAN> param_start_date</SPAN><SPAN> and:</SPAN><SPAN> param_end_date </SPAN></LI> </UL> <H3><SPAN>Page-level filters</SPAN></H3> <P><I><SPAN>Use </SPAN></I><STRONG><I>Page-level filters</I></STRONG><I><SPAN> when a control should affect only one page. For example, a detail page needs its own local “Product Category” separate from a summary page.</SPAN></I></P> <P><SPAN>The image above shows Nation as a Page filter. </SPAN></P> <UL> <LI style="font-weight: 400;" aria-level="1"><SPAN>All charts and tables in this page that use the Nation as a Predicate will respect the selections </SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>Other pages will not be affected by this filter.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>There can be one or more filters on a page to filter the data. For example, there could be a “</SPAN><SPAN>Segment</SPAN><SPAN>” filter in addition to a Nation filter</SPAN><SPAN> </SPAN></LI> </UL> <P> </P> <H3><SPAN>Widget-level (static) filters</SPAN></H3> <P><I><SPAN>Use </SPAN></I><STRONG><I>widget-level (static) filters</I></STRONG><I><SPAN> when the value must be fixed and unchangeable for a given chart (for example, side-by-side charts permanently pinned to different regions). </SPAN></I><SPAN>The image above shows s_name (supplier name) as a Widget filter. </SPAN></P> <P><SPAN>(Note that this is hidden when the dashboard is published).</SPAN></P> <UL> <LI style="font-weight: 400;" aria-level="1"><SPAN>The underlying dataset shows all suppliers, but only one Supplier is shown in the chart</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>Other Examples: a bar chart that shows revenue by </SPAN><SPAN>o.o_orderstatus</SPAN><SPAN> with a widget-level filter restricting </SPAN><SPAN>status IN ('F','O')</SPAN><SPAN>.</SPAN></LI> </UL> <P> </P> <H2><STRONG>When to use Parameters vs Filter fields</STRONG></H2> <P><SPAN>Databricks supports both field filters and parameters, which behave differently and have distinct performance characteristics. </SPAN></P> <P><SPAN>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.</SPAN></P> <H3><STRONG>Filter fields</STRONG></H3> <P><SPAN>Filter fields operate on fields in the dataset (such as </SPAN><SPAN>status</SPAN><SPAN> or </SPAN><SPAN>segment</SPAN><SPAN>). </SPAN></P> <P><SPAN>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. </SPAN></P> <P><SPAN>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. </SPAN></P> <P><SPAN>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.</SPAN></P> <UL> <LI style="font-weight: 400;" aria-level="1"><SPAN>Best for:</SPAN></LI> <UL> <LI style="font-weight: 400;" aria-level="2"><SPAN>Simple, local filtering on small to medium result sets.</SPAN></LI> <LI style="font-weight: 400;" aria-level="2"><SPAN>Cases where you only need to affect one or a few visuals.</SPAN></LI> </UL> <LI style="font-weight: 400;" aria-level="1"><SPAN> Consider the sample SQL and output from the Data tab in the Dashboard. </SPAN></LI> </UL> <P> </P> <P><SPAN>WITH </SPAN><SPAN>supp_norm </SPAN><SPAN>AS </SPAN><SPAN>(</SPAN></P> <P><SPAN> </SPAN><SPAN>SELECT    </SPAN><SPAN>n.n_name </SPAN><SPAN>AS </SPAN><SPAN>nation_name, p.p_name as part_name,  s.s_name, ps.ps_supplycost,   p.p_retailprice</SPAN></P> <P><SPAN> </SPAN><SPAN>FROM </SPAN><SPAN>samples.tpch.supplier s</SPAN></P> <P><SPAN> </SPAN><SPAN>JOIN </SPAN><SPAN>samples.tpch.partsupp ps </SPAN><SPAN>ON </SPAN><SPAN>s.s_suppkey = ps.ps_suppkey</SPAN></P> <P><SPAN> </SPAN><SPAN>JOIN </SPAN><SPAN>samples.tpch.part p   </SPAN><SPAN>ON </SPAN><SPAN>ps.ps_partkey = p.p_partkey</SPAN></P> <P><SPAN> </SPAN><SPAN>JOIN </SPAN><SPAN>samples.tpch.nation n </SPAN><SPAN>ON </SPAN><SPAN>s.s_nationkey = n.n_nationkey</SPAN></P> <P><SPAN>)</SPAN></P> <P><SPAN>SELECT   </SPAN><SPAN>nation_name, s_name, part_name, </SPAN><SPAN>SUM</SPAN><SPAN>(p_retailprice) </SPAN><SPAN>AS </SPAN><SPAN>netprice,   </SPAN><SPAN>SUM</SPAN><SPAN>(ps_supplycost) </SPAN><SPAN>AS </SPAN><SPAN>part_cost</SPAN></P> <P><SPAN>FROM </SPAN><SPAN>supp_norm</SPAN></P> <P><SPAN>GROUP BY </SPAN><SPAN>nation_name, s_name, part_name</SPAN></P> <P><BR /><BR /></P> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="User16844985548_2-1771461822166.png" style="width: 400px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/24139iC72CA6205DAE4BEB/image-size/medium?v=v2&px=400" role="button" title="User16844985548_2-1771461822166.png" alt="User16844985548_2-1771461822166.png" /></span></P> <P> </P> <P> </P> <P><SPAN>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 - </SPAN><I><SPAN>chiffon red peru coral dim</SPAN></I><SPAN>” in the title. If we want to show two charts with different filter values side by side, this method is useful.</SPAN></P> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="User16844985548_3-1771461822168.png" style="width: 400px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/24142iEA1F0F294088DDB1/image-size/medium?v=v2&px=400" role="button" title="User16844985548_3-1771461822168.png" alt="User16844985548_3-1771461822168.png" /></span></P> <P> </P> <P><BR /><BR /><BR /></P> <P><SPAN>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.</SPAN></P> <P> </P> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="User16844985548_4-1771461822169.png" style="width: 400px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/24143iF9AD3260869279B2/image-size/medium?v=v2&px=400" role="button" title="User16844985548_4-1771461822169.png" alt="User16844985548_4-1771461822169.png" /></span></P> <P> </P> <H3><SPAN>Parameter filters</SPAN></H3> <P><SPAN>Parameter filters correspond to query parameters and change the dataset SQL when they change.  The parameters in a query are coded using</SPAN><SPAN>:param_name</SPAN><SPAN>, where param_name can be any predicate like nation, date, thresholds, etc. </SPAN></P> <UL> <LI style="font-weight: 400;" aria-level="1"><SPAN>Best for:</SPAN></LI> <UL> <LI style="font-weight: 400;" aria-level="2"><SPAN>Large datasets where you need to filter early.</SPAN></LI> <LI style="font-weight: 400;" aria-level="2"><SPAN>Multi-select logic inside </SPAN><SPAN>WHERE</SPAN><SPAN> or </SPAN><SPAN>HAVING</SPAN><SPAN>.</SPAN></LI> <LI style="font-weight: 400;" aria-level="2"><SPAN>Thresholds (like minimum revenue) or time-grain switches.  </SPAN></LI> </UL> <LI style="font-weight: 400;" aria-level="1"><SPAN> This example uses a query called Revenue Trends by Nation, and we use a parameter called</SPAN><SPAN>:nation </SPAN><SPAN>for the column</SPAN><SPAN> n_name</SPAN><SPAN> in the nation table. The parameter details in the Data panel are shown with a selection as</SPAN><SPAN> ‘ALGERIA’</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>The query definition and the parameter definition are shown below. Note that </SPAN><STRONG>Allow multiple selections</STRONG><SPAN> is not enabled. </SPAN></LI> </UL> <P> </P> <P><SPAN>-- Revenue Trends by Nation to be used in Order Analysis page</SPAN></P> <P><SPAN>SELECT</SPAN></P> <P><SPAN>   o_orderdate, n_name </SPAN><SPAN>AS</SPAN><SPAN> nation, o_orderpriority </SPAN><SPAN>as</SPAN><SPAN> Priority,</SPAN></P> <P><SPAN>   </SPAN><SPAN>sum</SPAN><SPAN>(l_extendedprice </SPAN><SPAN>*</SPAN><SPAN> (</SPAN><SPAN>1</SPAN> <SPAN>-</SPAN><SPAN> l_discount) </SPAN><SPAN>*</SPAN><SPAN> (((length(n_name))</SPAN><SPAN>/</SPAN><SPAN>100</SPAN><SPAN>) </SPAN><SPAN>+</SPAN><SPAN> (</SPAN><SPAN>year</SPAN><SPAN>(o_orderdate)</SPAN><SPAN>-</SPAN><SPAN>1993</SPAN><SPAN>)</SPAN><SPAN>/</SPAN><SPAN>100</SPAN><SPAN>)) </SPAN><SPAN>AS</SPAN><SPAN> revenue</SPAN></P> <P><SPAN>FROM</SPAN></P> <P><SPAN>   </SPAN><SPAN>`</SPAN><SPAN>samples</SPAN><SPAN>`</SPAN><SPAN>.</SPAN><SPAN>`</SPAN><SPAN>tpch</SPAN><SPAN>`</SPAN><SPAN>.</SPAN><SPAN>`</SPAN><SPAN>customer</SPAN><SPAN>`</SPAN><SPAN>,  </SPAN><SPAN>`</SPAN><SPAN>samples</SPAN><SPAN>`</SPAN><SPAN>.</SPAN><SPAN>`</SPAN><SPAN>tpch</SPAN><SPAN>`</SPAN><SPAN>.</SPAN><SPAN>`</SPAN><SPAN>orders</SPAN><SPAN>`</SPAN><SPAN>,</SPAN></P> <P><SPAN>   </SPAN><SPAN>`</SPAN><SPAN>samples</SPAN><SPAN>`</SPAN><SPAN>.</SPAN><SPAN>`</SPAN><SPAN>tpch</SPAN><SPAN>`</SPAN><SPAN>.</SPAN><SPAN>`</SPAN><SPAN>lineitem</SPAN><SPAN>`</SPAN><SPAN>,  </SPAN><SPAN>`</SPAN><SPAN>samples</SPAN><SPAN>`</SPAN><SPAN>.</SPAN><SPAN>`</SPAN><SPAN>tpch</SPAN><SPAN>`</SPAN><SPAN>.</SPAN><SPAN>`</SPAN><SPAN>nation</SPAN><SPAN>`</SPAN></P> <P><SPAN>WHERE</SPAN></P> <P><SPAN>   c_custkey </SPAN><SPAN>=</SPAN><SPAN> o_custkey</SPAN></P> <P><SPAN>   </SPAN><SPAN>AND</SPAN><SPAN> l_orderkey </SPAN><SPAN>=</SPAN><SPAN> o_orderkey</SPAN></P> <P><SPAN>   </SPAN><SPAN>AND</SPAN><SPAN> c_nationkey </SPAN><SPAN>=</SPAN><SPAN> n_nationkey</SPAN></P> <P><SPAN>   </SPAN><SPAN>AND</SPAN><SPAN>  n_name </SPAN><SPAN>=</SPAN><SPAN> :nation</SPAN></P> <P><SPAN>GROUP BY</SPAN><SPAN>  o_orderdate, Priority, nation</SPAN></P> <P><SPAN>ORDER BY</SPAN><SPAN>  nation </SPAN><SPAN>ASC</SPAN><SPAN>, Priority </SPAN><SPAN>ASC</SPAN><SPAN>, o_orderdate </SPAN><SPAN>ASC</SPAN><SPAN>;</SPAN></P> <P> </P> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="User16844985548_5-1771461822170.png" style="width: 400px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/24141i282F58B1DCE10861/image-size/medium?v=v2&px=400" role="button" title="User16844985548_5-1771461822170.png" alt="User16844985548_5-1771461822170.png" /></span></P> <P> </P> <P> </P> <P><SPAN>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.</SPAN></P> <P> </P> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="User16844985548_6-1771461822170.png" style="width: 400px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/24145iE8D2971DF25B91E9/image-size/medium?v=v2&px=400" role="button" title="User16844985548_6-1771461822170.png" alt="User16844985548_6-1771461822170.png" /></span></P> <P> </P> <P> </P> <P><SPAN>The Revenue Trend by Nation chart should use the same dataset - Revenue Trends by Nation. </SPAN></P> <P> </P> <P><SPAN>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. </SPAN></P> <P> </P> <P><SPAN>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.</SPAN></P> <P> </P> <TABLE> <TBODY> <TR> <TD> <P><STRONG>Scenario</STRONG></P> </TD> <TD> <P><STRONG>Filter fields emphasis</STRONG></P> </TD> <TD> <P><STRONG>Parameters emphasis</STRONG></P> </TD> </TR> <TR> <TD> <P><SPAN>Need quick UX on small datasets</SPAN></P> </TD> <TD> <P><SPAN>Browser-side result filtering.</SPAN></P> </TD> <TD> <P><SPAN>Accept query reruns for pre-aggregation filtering.</SPAN><A href="https://docs.databricks.com/aws/en/dashboards/filter-types" target="_blank" rel="noopener"><SPAN> </SPAN></A></P> </TD> </TR> <TR> <TD> <P><SPAN>Need complex SQL conditions</SPAN></P> </TD> <TD> <P><SPAN>Limited to end-of-query filters.</SPAN></P> </TD> <TD> <P><SPAN>Conditions placed anywhere in SQL logic.</SPAN><A href="https://docs.databricks.com/aws/en/dashboards/filter-types" target="_blank" rel="noopener"><SPAN> </SPAN></A></P> </TD> </TR> <TR> <TD> <P><SPAN>Cascading cross-dataset controls</SPAN></P> </TD> <TD> <P><SPAN>Natural fit across shared fields.</SPAN><A href="https://docs.databricks.com/aws/en/dashboards/filter-types" target="_blank" rel="noopener"><SPAN> </SPAN></A></P> </TD> <TD> <P><SPAN>Requires coordinating shared parameters.</SPAN><A href="https://docs.databricks.com/aws/en/dashboards/filter-types" target="_blank" rel="noopener"><SPAN> </SPAN></A></P> </TD> </TR> <TR> <TD> <P><SPAN>Centralized dashboard controls</SPAN></P> </TD> <TD> <P><SPAN>Tied mainly to their datasets.</SPAN></P> </TD> <TD> <P><SPAN>Strong option for reusable, dashboard-level knobs.</SPAN><A href="https://docs.databricks.com/aws/en/dashboards/filter-types" target="_blank" rel="noopener"><SPAN> </SPAN></A></P> </TD> </TR> </TBODY> </TABLE> <H2><STRONG>Deep dive: parameter patterns with “All” and multi-select</STRONG></H2> <P><SPAN>Parameters become especially powerful when you design them to support “All” and multi-select behavior.</SPAN></P> <H3><SPAN>Single-select “All” pattern</SPAN></H3> <P><SPAN>For a single-select parameter, use a sentinel like </SPAN><SPAN>'All'</SPAN><SPAN> and an </SPAN><SPAN>OR</SPAN><SPAN> condition in the </SPAN><SPAN>WHERE</SPAN><SPAN> 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.</SPAN></P> <P><SPAN>To achieve this, we change the predicate from </SPAN><SPAN><BR /></SPAN><SPAN> </SPAN></P> <P><SPAN>   </SPAN><SPAN>AND</SPAN><SPAN>  n_name </SPAN><SPAN>=</SPAN><SPAN> :nation</SPAN></P> <P><SPAN>TO</SPAN></P> <P><SPAN>   </SPAN><SPAN>AND</SPAN><SPAN> (:nation </SPAN><SPAN>=</SPAN> <SPAN>"All"</SPAN> <SPAN>OR</SPAN><SPAN> n_name </SPAN><SPAN>=</SPAN><SPAN> :nation)</SPAN></P> <P> </P> <P><SPAN>Another way to accomplish this is </SPAN></P> <P> </P> <P><SPAN>  </SPAN><SPAN>AND</SPAN><SPAN>  </SPAN><SPAN>IF</SPAN><SPAN> (:nation </SPAN><SPAN>=</SPAN> <SPAN>"All"</SPAN><SPAN>, </SPAN><SPAN>true</SPAN><SPAN>, n_name </SPAN><SPAN>=</SPAN><SPAN> :nation)</SPAN></P> <H2><SPAN> </SPAN><SPAN>Multi-select with </SPAN><SPAN>ARRAY_CONTAINS</SPAN></H2> <P><SPAN>For multi-select, allow multiple selections in the parameter and use </SPAN><SPAN>ARRAY_CONTAINS</SPAN><SPAN> in the predicate. This will help us to select one or many or ‘All Nations’</SPAN></P> <P><SPAN>There are three changes to make for this:</SPAN></P> <OL> <LI style="font-weight: 400;" aria-level="1"><SPAN>In the query, change the condition</SPAN></LI> </OL> <P><SPAN> </SPAN><SPAN>AND</SPAN><SPAN> (:nation </SPAN><SPAN>=</SPAN> <SPAN>"All"</SPAN> <SPAN>OR</SPAN><SPAN> n_name </SPAN><SPAN>=</SPAN><SPAN> :nation)</SPAN></P> <P><SPAN>TO </SPAN><SPAN><BR /></SPAN> <SPAN>AND</SPAN><SPAN> ( ARRAY_CONTAINS(:nation,</SPAN><SPAN>"All"</SPAN><SPAN>)  </SPAN><SPAN>OR</SPAN><SPAN> ARRAY_CONTAINS(:nation,n_name))</SPAN></P> <OL> <LI style="font-weight: 400;" aria-level="1"><SPAN>Select </SPAN><STRONG>Allow multiple selections</STRONG><SPAN> checkbox in the parameter details panel.</SPAN><SPAN><BR /></SPAN><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="User16844985548_7-1771461822172.png" style="width: 400px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/24144i3680AFEC4A93BAE3/image-size/medium?v=v2&px=400" role="button" title="User16844985548_7-1771461822172.png" alt="User16844985548_7-1771461822172.png" /></span> <P> </P> </LI> </OL> <P><BR /><BR /><BR /></P> <OL> <LI style="font-weight: 400;" aria-level="1"><SPAN>In the Dashboard page, we change the filter widget to </SPAN><STRONG>Multiple values</STRONG><SPAN> 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 </SPAN><I><SPAN>nation</SPAN></I><SPAN> filter, while the chart does not have any filters</SPAN></LI> </OL> <P><SPAN> </SPAN></P> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="User16844985548_8-1771461822173.png" style="width: 400px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/24146i4D787325859C09B1/image-size/medium?v=v2&px=400" role="button" title="User16844985548_8-1771461822173.png" alt="User16844985548_8-1771461822173.png" /></span></P> <P> </P> <P><BR /><BR /></P> <H2><STRONG>Building query-based dropdowns</STRONG></H2> <P><SPAN>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.</SPAN></P> <P><STRONG>Step 1. </STRONG><SPAN>To define a drop-down list of valid nations, we build a query to extract all the Nations  </SPAN><SPAN><BR /><BR /></SPAN></P> <P><SPAN> </SPAN></P> <P><SPAN>—  Nation_list query to use as a Drop down</SPAN></P> <P><SPAN>SELECT </SPAN></P> <P><SPAN>distinct</SPAN><SPAN> n_name </SPAN><SPAN>as</SPAN><SPAN> nation_name</SPAN></P> <P><SPAN>FROM</SPAN></P> <P><SPAN> </SPAN><SPAN>samples</SPAN><SPAN>.</SPAN><SPAN>tpch</SPAN><SPAN>.nation</SPAN></P> <P> </P> <P><STRONG>Step 2</STRONG><SPAN>. We associate this on the Order Analysis page as a field in the </SPAN><STRONG>Nation_list</STRONG><SPAN> with </SPAN><STRONG>Multiple values</STRONG><SPAN>. We use the nation in the Nation_list as a Filter, which effectively gives us the drop-down list. </SPAN></P> <P><SPAN>The Parameters will still point to the column Nation in the Revenue Trends by Nation </SPAN></P> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="User16844985548_9-1771461822173.png" style="width: 400px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/24147i91E3C1E4505A9ABA/image-size/medium?v=v2&px=400" role="button" title="User16844985548_9-1771461822173.png" alt="User16844985548_9-1771461822173.png" /></span></P> <P> </P> <P><BR /><BR /></P> <P><SPAN>Now we have a drop-down list of values for Nations that allows multiple selections.</SPAN></P> <P> </P> <H2><STRONG>Global Filters</STRONG></H2> <P><SPAN>To show this we will add a date range filter to the Revenue Trends by Nation dataset query.</SPAN><SPAN><BR /></SPAN><SPAN><BR /></SPAN><SPAN>AND</SPAN><SPAN>  o_orderdate  </SPAN><SPAN>BETWEEN</SPAN><SPAN> :</SPAN><SPAN>order_date</SPAN><SPAN>.</SPAN><SPAN>min</SPAN> <SPAN>AND</SPAN><SPAN> :</SPAN><SPAN>order_date</SPAN><SPAN>.</SPAN><SPAN>max</SPAN></P> <P> </P> <P><SPAN>Edit the parameter type to </SPAN><STRONG>Date Range</STRONG><SPAN>. </SPAN></P> <P><SPAN>Use the calendar pop-ups to select a default date range and execute the query.</SPAN></P> <P> </P> <P><SPAN>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.  </SPAN><SPAN><BR /><BR /></SPAN></P> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="User16844985548_10-1771461822175.png" style="width: 400px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/24148iB044A63F545D9891/image-size/medium?v=v2&px=400" role="button" title="User16844985548_10-1771461822175.png" alt="User16844985548_10-1771461822175.png" /></span></P> <P><BR /><BR /></P> <P><SPAN>Click the Date Range and choose different dates, and see the values change. </SPAN><SPAN><BR /></SPAN><SPAN>In a similar way, we can associate more datasets with this Global Filter and by adding them as parameters. </SPAN></P> <P><STRONG>To Try further:</STRONG></P> <P><SPAN>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.</SPAN></P> <P><SPAN> Use this query to build a data set called Revenue by Order Priority. Ensure the parameter is defined as a Date Range</SPAN></P> <P> </P> <P><SPAN>SELECT</SPAN></P> <P><SPAN>   </SPAN><SPAN>o.o_orderpriority </SPAN><SPAN>AS </SPAN><SPAN>priority,</SPAN></P> <P><SPAN>   l.l_shipmode </SPAN><SPAN>AS </SPAN><SPAN>ship_mode,</SPAN></P> <P><SPAN>   </SPAN><SPAN>COUNT</SPAN><SPAN>(*) </SPAN><SPAN>AS </SPAN><SPAN>order_count,</SPAN></P> <P><SPAN>   o.o_orderdate</SPAN></P> <P><SPAN>FROM    </SPAN><SPAN>samples.tpch.orders </SPAN><SPAN>AS </SPAN><SPAN>o</SPAN></P> <P><SPAN>JOIN    </SPAN><SPAN>samples.tpch.lineitem </SPAN><SPAN>AS </SPAN><SPAN>l</SPAN></P> <P><SPAN>ON    </SPAN><SPAN>o.o_orderkey = l.l_orderkey</SPAN></P> <P><SPAN>WHERE </SPAN><SPAN>o.o_orderdate </SPAN><SPAN>BETWEEN </SPAN><SPAN>:order_date.min and :order_date.max</SPAN></P> <P><SPAN>GROUP BY    </SPAN><SPAN>o.o_orderpriority, l.l_shipmode, o.o_orderdate</SPAN></P> <P><SPAN>ORDER BY  </SPAN><SPAN>priority, ship_mode;</SPAN></P> <P><BR /><BR /></P> <P><SPAN>Add a chart to show Revenue by Order Priority. </SPAN></P> <P><SPAN>Add the Global Filter to this dataset. </SPAN></P> <P> </P> <P><SPAN>Add the Priority Filter as a Page Filter on this page. Associate the Priority column as a filter. </SPAN></P> <P><SPAN>You should see something like this depending on your selections</SPAN></P> <P> </P> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="User16844985548_11-1771462095748.png" style="width: 400px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/24149i349A7F20C9A16FA8/image-size/medium?v=v2&px=400" role="button" title="User16844985548_11-1771462095748.png" alt="User16844985548_11-1771462095748.png" /></span></P> <P> </P> <P><SPAN> <STRONG>References:</STRONG></SPAN></P> <P><SPAN> </SPAN><SPAN>Databricks Dashboard Parameters and Filters: </SPAN></P> <UL> <LI style="font-weight: 400;" aria-level="1"><A href="https://docs.databricks.com/aws/en/dashboards/parameters" target="_blank" rel="noopener"><SPAN>https://docs.databricks.com/aws/en/dashboards/parameters</SPAN></A>. </LI> <LI style="font-weight: 400;" aria-level="1"><SPAN> </SPAN><A href="https://docs.databricks.com/aws/en/dashboards/filters" target="_blank" rel="noopener"><SPAN>https://docs.databricks.com/aws/en/dashboards/filters</SPAN></A></LI> </UL> <P><SPAN> </SPAN></P> Thu, 26 Feb 2026 15:26:05 GMT JJaiwant 2026-02-26T15:26:05Z Design Filters and Parameters That Scale in Databricks AI/BI Dashboards https://community.databricks.com/t5/technical-blog/design-filters-and-parameters-that-scale-in-databricks-ai-bi/ba-p/148764 <P><SPAN><SPAN><BR /></SPAN></SPAN><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="JJaiwant_0-1772053582243.png" style="width: 400px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/24334i3D588E2AFAB9570F/image-size/medium?v=v2&px=400" role="button" title="JJaiwant_0-1772053582243.png" alt="JJaiwant_0-1772053582243.png" /></span></P> <P> </P> <P><SPAN> </SPAN></P> Thu, 26 Feb 2026 15:26:05 GMT https://community.databricks.com/t5/technical-blog/design-filters-and-parameters-that-scale-in-databricks-ai-bi/ba-p/148764 JJaiwant 2026-02-26T15:26:05Z