How filters become SQL

Every question you ask of a semantic layer compiles to one SQL query, which runs in your data source. A filter’s kind decides where in that query its condition lands, and so which rows it can remove: rows of one table before the join, joined rows, rows inside one measure, or finished groups.

Filter	Where it is set	Where it lands
Pre-filter	A table’s Pre-filter	WHERE on that table alone, before the join
Global data reduction	Apply as global data reduction on a pre-filter	WHERE on the joined rows of every query that reaches the table’s join group (with Strict off, only queries with a measure from that group)
Question filter	Filters on a business question; Filter in Data Explorer	WHERE on the joined rows
Measure filter	Measure filter on a measure	CASE WHEN inside that measure’s aggregation
Result filter	Result filters on a business question or in Data Explorer	HAVING, after the aggregation
Set filter	Set Filter	A list of the matching entities, joined to the main query

Pre-filters, global data reductions and question filters combine with AND. A question filter and a global data reduction on the same query both apply, and when they contradict each other the query returns no rows.

To read the SQL a query ran, filters included, select View query insights in Data Explorer. A business question’s SQL tab shows only what the question groups by and reports; its filters are applied around that statement and do not appear in it. The examples below are abridged Snowflake output for a small model in which Sales joins to Clients and Products.

A table pre-filter

A pre-filter is applied to its table on its own, before any join, and only in queries that use the table.

WITH CLI AS (
  SELECT CLIENTID, CLIENTNAME
  FROM CLIENTS
  WHERE CLIENTGENDER = 'Female'          -- the pre-filter
)
SELECT CLI.CLIENTNAME, SUM(SAL.SALESAMOUNT)
FROM SALES AS SAL
LEFT JOIN CLI ON SAL.SALESCLIENT = CLI.CLIENTID
GROUP BY CLI.CLIENTNAME

The join keeps every sales row. Sales whose client the pre-filter removed are still counted, under an empty client name, and a query that uses no field of Clients is not filtered at all. A pre-filter tidies one table; it does not restrict the rows of the tables joined to it.

A global data reduction

With Apply as global data reduction on, the pre-filter no longer runs on its table alone. Its condition is added to the WHERE of every query that reaches the table’s join group, and the table is joined in to evaluate it, even when the query uses none of its fields. A list of product categories, with Strict on:

SELECT PRD.PRODUCTCAT
FROM SALES AS SAL
LEFT JOIN CLIENTS AS CLI ON SAL.SALESCLIENT = CLI.CLIENTID
LEFT JOIN PRODUCTS AS PRD ON SAL.PRODUCT = PRD.PRODUCTID
WHERE CLI.CLIENTGENDER = 'Female'        -- the reduction
GROUP BY PRD.PRODUCTCAT

Only categories with sales to the permitted clients come back. With Strict off, the reduction applies only to queries with a measure from the join group, so the same list reads Products alone and returns every category. Tables with no join path to the reduction’s table are never affected. Restrict rows with a security rule covers when to use it.

A question filter

A filter on a business question, or one set in Data Explorer, lands in the WHERE of the joined rows, so it can test a field of any table the query joins.

SELECT CLI.CLIENTNAME, SUM(SAL.SALESAMOUNT)
FROM SALES AS SAL
LEFT JOIN CLIENTS AS CLI ON SAL.SALESCLIENT = CLI.CLIENTID
LEFT JOIN PRODUCTS AS PRD ON SAL.PRODUCT = PRD.PRODUCTID
WHERE PRD.PRODUCTCAT = 'A'               -- the question filter
GROUP BY CLI.CLIENTNAME

When a join gives each measured row many rows of another table, a filter on that table’s fields cannot be evaluated row by row; Filters that cannot reach a measure’s rows explains what is refused.

A measure filter

A measure’s own filter becomes a CASE WHEN inside its aggregation, so two measures on the same rows can each count a different slice without filtering the query.

SELECT
  CLI.CLIENTNAME,
  SUM(SAL.SALESAMOUNT),                                          -- no filter
  SUM(CASE WHEN SAL.SALESQUANTITY > 5 THEN SAL.SALESAMOUNT ELSE 0 END)  -- measure filter
FROM SALES AS SAL
LEFT JOIN CLIENTS AS CLI ON SAL.SALESCLIENT = CLI.CLIENTID
GROUP BY CLI.CLIENTNAME

A group with no matching rows therefore shows 0 for a Sum; Use null in measure filter makes it blank instead. See measures for setting a Measure filter.

A result filter

A result filter tests the aggregated value, so it lands in HAVING, after the GROUP BY. It removes groups from the answer; it does not change which rows were aggregated.

SELECT CLI.CLIENTNAME, SUM(SAL.SALESAMOUNT) AS m1
FROM SALES AS SAL
LEFT JOIN CLIENTS AS CLI ON SAL.SALESCLIENT = CLI.CLIENTID
GROUP BY CLI.CLIENTNAME
HAVING m1 > 100                          -- the result filter

A result filter on a measure with an advanced calculation runs later, after the calculation; see Result filters and advanced calculations.

A set filter

A set filter selects entities by their related records. Each branch becomes its own query listing the matching entity keys; the branches are combined with INTERSECT or a union, a Not branch keeps the entities missing from its list, and the result is joined to the main query.

WITH a1 AS (                             -- clients who bought Product 01
  SELECT CLI.CLIENTID AS d1
  FROM SALES AS SAL
  LEFT JOIN CLIENTS AS CLI ON SAL.SALESCLIENT = CLI.CLIENTID
  LEFT JOIN PRODUCTS AS PRD ON SAL.PRODUCT = PRD.PRODUCTID
  WHERE PRD.PRODUCTNAME = 'Product 01'
  GROUP BY CLI.CLIENTID
)
SELECT CLI.CLIENTNAME, SUM(SAL.SALESAMOUNT)
FROM SALES AS SAL
LEFT JOIN CLIENTS AS CLI ON SAL.SALESCLIENT = CLI.CLIENTID
JOIN a1 ON CLI.CLIENTID = a1.d1          -- keep only clients in the set
GROUP BY CLI.CLIENTNAME

The join keeps every row of an entity in the set, not only the rows that matched the branch: a client who bought Product 01 keeps all their sales in the total. Associative filters shows how to build one.