Implementing AND-Style Multi-Select Filters in DataBrain

Multi-select filters in DataBrain typically behave as OR: “show rows where the field matches any of the selected values.” Sometimes you need AND: “show only entities that have all of the selected values” (e.g. countries that participate in both Archery and Basketball). This post gives a concrete recipe using custom SQL.

What you'll need: A metric that uses Custom SQL (or a complex filter), and a multi-select filter that you can apply as Variable for Custom SQL so its selected values are available in your WHERE/HAVING. Your data should have an "entity" column (e.g. country, account) and a dimension column (e.g. sport, product) that can appear multiple times per entity.

The problem

1. Default: Choosing “Archery” and “Basketball” in a Sport filter usually means: “sport is Archery or Basketball” → you see every country that has either sport.
2. Goal: “Show only countries that have both Archery and Basketball” → one or a few countries.

That’s an AND across multiple values of the same dimension, not just AND between two different filters.

Other examples: "Show accounts that have purchased both Product A and Product B" (not "A or B"). "Show regions that appear in both Q1 and Q2 datasets" (not "Q1 or Q2"). "Show customers who have used Feature X and Feature Y" (not "X or Y"). In each case, you're asking for entities that satisfy all selected values, which requires the AND pattern below.

Recipe: AND-style multi-select with custom SQL

You can get AND semantics by using a custom SQL metric that:

1. Accepts the selected filter values (e.g. via a variable or by treating the multi-select as a list).
2. Restricts to entities (e.g. countries) that appear in the data for every selected value.

Below is a generic pattern. Exact syntax depends on your database (Postgres, BigQuery, etc.) and how you pass the multi-select into the metric (variable vs. direct apply).

Pattern: “Entities that have all selected values”

Conceptually:

1. Let selected_values = the list of values the user selected (e.g. ['Archery', 'Basketball']).
2. For each entity (e.g. country), count how many of the selected_values it has in the data.
3. Keep only entities where that count equals the number of selected values.

Example (Postgres-style): table participations with country, sport.

-- Assume filter variable {{sports}} or similar delivers a list like 'Archery','Basketball'

-- and the metric is grouped by country. Then you want countries that have both sports.

SELECT
  "country" AS "country",
  COUNT(*) AS "participation_count"
FROM "public"."participations" AS p
WHERE "sport" IN ({{sports}})   -- OR clause from multi-select
GROUP BY "country"
HAVING COUNT(DISTINCT "sport") = (
  -- number of distinct selected sports must equal number of selected values
  (SELECT COUNT(*) FROM (SELECT unnest(string_to_array({{sports}}, ',')) AS s) AS t)
)

‍

In practice, how you pass {{sports}} and how you compute “number of selected values” depends on your filter setup (variable apply, custom SQL filter, or backend building the list). DataBrain passes multi-select values in a format you can use in the WHERE clause (e.g. comma-separated or list); adjust the HAVING count logic to match. For BigQuery, Snowflake, or Athena, use the same pattern with that engine's equivalent of COUNT(DISTINCT) and array/list length for the selected values. The important part is: filter with IN (OR) for the selected values, then HAVING COUNT(DISTINCT <dimension>) = <number of selected values> so only entities with all of them remain.

Simpler variant: two fixed values

If you only ever need “has both A and B” (e.g. Archery and Basketball), you can skip a dynamic list and use two conditions:

SELECT
  "country" AS "country",
  COUNT(*) AS "count"
FROM "public"."participations"
WHERE "sport" IN ('Archery', 'Basketball')
GROUP BY "country"
HAVING COUNT(DISTINCT "sport") = 2

‍

That yields only countries that have both sports. For a flexible multi-select AND, the list and the “2” need to come from the filter (e.g. variable or custom filter config).

Implementation checklist: (1) Create or open a custom SQL metric. (2) Add a multi-select filter and set its apply mode to Variable for Custom SQL, and note the variable name (e.g. {{value}} or a custom name). (3) In the metric SQL, add a WHERE with IN (variable) and a GROUP BY on the entity column. (4) Add the HAVING clause so that COUNT(DISTINCT <dimension>) equals the number of selected values. (5) Test with one, two, and three selected values to confirm only entities with all of them appear.

Alternative example: products and categories

The same pattern works for product/category data. Suppose you have orders with customer_id and product_category, and you want "customers who bought from both Electronics and Clothing":

SELECT
  "customer_id" AS "customer_id",
  COUNT(*) AS "order_count"
FROM "public"."orders"
WHERE "product_category" IN ({{categories}})
GROUP BY "customer_id"
HAVING COUNT(DISTINCT "product_category") = (
  (SELECT COUNT(*) FROM (SELECT unnest(string_to_array({{categories}}, ',')) AS c) AS t)
)

‍

Replace {{categories}} with your filter variable and adjust for your database's array syntax. The logic is identical: filter by the selected values (OR), then HAVING ensures each entity has all of them (AND).

Where to implement this in DataBrain

1. Custom SQL metric
2. Create a metric with Custom SQL.
3. If your multi-select is applied as Variable for Custom SQL, use the variable(s) in the WHERE/HAVING as above (only in WHERE/HAVING; see Conditional analytics post).
4. If the multi-select is applied directly, the engine generates an OR (IN) for you; to get AND you’d need to move that logic into custom SQL and use the HAVING pattern instead of relying on the default filter application.
5. Complex filter
6. If you can express “entity has all selected values” as a single condition (e.g. with a subquery), you can put that condition in a complex filter (custom WHERE clause) and keep the rest of the metric as usual.
7. Relation operator
8. Use AND between different filters (e.g. “Sport = Archery” AND “Region = Europe”) in the metric’s filter configuration. That’s AND between dimensions, not AND across multiple values of one dimension; for the latter, use the custom SQL / HAVING approach above.

Caveats: If no entity has all selected values, the result set will be empty—that's expected. If your dimension column can be NULL, decide whether NULL should count toward "all selected" or be excluded (your HAVING and WHERE should reflect that). When passing multiple values via a variable, ensure the format (e.g. comma-separated, quoted) matches what your database expects in the IN list.

Troubleshooting: If you see unexpected results—e.g. entities that only have one of the selected values—check that (a) the HAVING count logic correctly computes the number of selected values for your database, and (b) the variable substitution isn't double-quoting or malforming the list. For Postgres, string_to_array({{sports}}, ',') and unnest() are common; for BigQuery, use SPLIT() or UNNEST(ARRAY...); for Snowflake, SPLIT_TO_TABLE() or similar. Test with a hard-coded list first to isolate variable-format issues.

Summary

1. Default multi-select = OR (any of the selected values).
2. AND across multiple values of the same dimension = use custom SQL (or complex filter) with:
3. WHERE &lt;dimension&gt; IN (&lt;selected values&gt;) (OR),
4. GROUP BY &lt;entity&gt;,
5. HAVING COUNT(DISTINCT &lt;dimension&gt;) = &lt;number of selected values&gt;.

For AND between different filters, use the metric’s relation operator (AND) between those filter conditions.

Related:

1. Build conditional analytics in DataBrain (scope, AND/OR, dependents, variables)
2. Context-aware dashboards — section filters, saved views, and drill-down when combining AND logic with section-scoped filters.
3. Metric filter – Variable and Complex filter in the docs.