--- schema_version: "1" id: a1a0e207cb4351f998969503d710528e0f258da5b825bfd99e203476774ece72 title: Filtering collection: developer version: current locale: en-us portal_url: https://docs.salesforce.com/developer/current/en-us/data-cloud/semantic-layer/guides/query-api-in-depth-filtering.md canonical_url: https://developer.salesforce.com/docs/data/semantic-layer/guide/query-api-in-depth-filtering.html source_site: developer.salesforce.com content_type: documentation content_hash: bea8442d5f83460f48795410eb06229e0d86b9d359f7bc58acd1c05781f61110 products: - Tableau Semantics Layer APIs - Data 360 Developer Documentation --- # Filtering ## Overview Filters define which records a semantic query returns. Apply filters to dimensions, measurements, and calculated fields to narrow your result set. The Semantic Query API supports multiple filter types that operate at different stages of query execution: context filters run before all other filters, standard query filters apply to pre-aggregation data, and aggregate filters (HAVING) operate on post-aggregation results. The Semantic Query API supports two operator representations: - **PascalCase operators** (`"GreaterThan"`, `"In"`, `"StartsWith"`) in `filters[]` arrays within `flatten_filter` structures and model-defined filters. - **SCREAMING_SNAKE operators** (`BINARY_OPERATOR_GREATER_THAN`, `BINARY_OPERATOR_IN`) in structured `binary_predicate` filter objects. Both forms are semantically equivalent; the wire representation differs by context. ## Metadata in the model Filters can be defined at authoring time as part of the Semantic Data Model. Model-level filters apply globally to all queries against that model, while Semantic Data Object filters apply only when that object is queried. For details on defining filters in the model, see [Semantic Data Object Field](https://developer.salesforce.com/docs/data/semantic-layer/guide/connect-api-in-depth-entities.md#semantic-data-object-field-semanticdataobjectfield) in the Authoring API. Filters are evaluated in layers. Semantic Data Object filters apply first, followed by relationships, then model-level (global) filters. Context filters are applied as an independent pre-filter, and aggregate filters (HAVING) are applied last, after aggregation. Within a single layer, conditions can be combined with OR; between layers, the layers are combined with AND. ## Query usage ### Comparison operators Use comparison operators to filter numeric, date, datetime, and text fields. Supported operators include equals, not equals, greater than, less than, greater than or equal, and less than or equal. Case-insensitive variants are available for text comparisons. The structured predicate form uses `BINARY_OPERATOR_*` constants: - `BINARY_OPERATOR_EQUALS` - `BINARY_OPERATOR_NOT_EQUAL_TO` - `BINARY_OPERATOR_GREATER_THAN` - `BINARY_OPERATOR_LESS_THAN` - `BINARY_OPERATOR_GREATER_THAN_OR_EQUAL_TO` - `BINARY_OPERATOR_LESS_THAN_OR_EQUAL_TO` - `BINARY_OPERATOR_EQUALS_IGNORE_CASE` - `BINARY_OPERATOR_NOT_EQUALS_IGNORE_CASE` ```json { "structuredSemanticQuery": { "fields": [ { "expression": { "table_field": { "name": "Account Name", "table_name": "AccountSemanticLayer__dll" } } }, { "expression": { "table_field": { "name": "Annual Revenue", "table_name": "AccountSemanticLayer__dll" } } } ], "filter": { "logical_binary_predicate": { "left_predicate": { "binary_predicate": { "left_expression": { "table_field": { "name": "Account Name", "table_name": "AccountSemanticLayer__dll" } }, "binary_operator": "BINARY_OPERATOR_NOT_EQUAL_TO", "right_expression": { "string_expression": "Richard Schneider" } } }, "logical_binary_operator": "LOGICAL_BINARY_OPERATOR_AND", "right_predicate": { "binary_predicate": { "left_expression": { "table_field": { "name": "Annual Revenue", "table_name": "AccountSemanticLayer__dll" } }, "binary_operator": "BINARY_OPERATOR_GREATER_THAN", "right_expression": { "int_expression": 0 } } } } }, "options": { "detailed_rows": true } }, "semanticModelId": "2SMxx0000004CAeGAM" } ``` ### Text operators Filter text fields using pattern-matching operators. Text operators support case-sensitive and case-insensitive variants. In `flatten_filter` PascalCase form: - `Contains` / `ContainsIgnoreCase` - `NotContains` / `DoesNotContainIgnoreCase` - `StartsWith` / `StartsWithIgnoreCase` - `DoesNotStartWith` / `DoesNotStartWithIgnoreCase` - `EndsWith` / `EndsWithIgnoreCase` - `DoesNotEndWith` / `DoesNotEndWithIgnoreCase` In structured predicate SCREAMING_SNAKE form: - `BINARY_OPERATOR_CONTAINS` - `BINARY_OPERATOR_STARTS_WITH` - `BINARY_OPERATOR_ENDS_WITH` (with corresponding `_IGNORE_CASE` and `NOT_*` variants) ```json { "structuredSemanticQuery": { "fields": [ { "expression": { "table_field": { "name": "semantic__Name__c", "table_name": "SemanticAccount__dlm" } } } // ... ] }, "semanticModel": { "apiName": "Sales", "label": "Sales", "semanticDataObjects": [ { "apiName": "SemanticAccount__dlm", "dataObjectName": "ssot__Account__dlm", "dataObjectType": "Dmo", "filterLogic": "1", "filters": [ { "fieldName": "SemanticAccount__dlm.semantic__Name__c", "operator": "StartsWith", "value": "Richard" } ], "semanticDimensions": [ // ... ] } ] } } ``` ### Set-membership operators Use `In` and `NotIn` operators to filter against a list of values. The `Values` operator is deprecated; use `In` instead. In flatten_filter PascalCase form: `"operator": "In"` or `"operator": "NotIn"` with a `"values": [...]` array. In structured predicate form: `BINARY_OPERATOR_IN` or `BINARY_OPERATOR_NOT_IN`. ```json { "semanticModel": { "apiName": "Sales", "semanticDataObjects": [ { "apiName": "SemanticAccount__dlm", "filterLogic": "1", "filters": [ { "fieldName": "SemanticAccount__dlm.semantic__Name__c", "operator": "NotIn", "values": ["InvalidName", "InvalidName2"] } ], "semanticDimensions": [ // ... ] } ], "semanticCalculatedMeasurements": [ { "apiName": "inner_formula_measurement_example", "filterLogic": "1", "filters": [ { "fieldName": "SemanticAccount__dlm.Semantic_KQ_Id__c", "operator": "In", "values": [50, 100] } ], "expression": "100 + SemanticAccount__dlm.Annual_Revenue_Amount" } ] } } ``` > **Performance:** Use `In` and `NotIn` with a value list instead of chaining `Equals` conditions with `OR`. ### Null and empty operators Distinguish between `IsNull` / `IsNotNull` (any data type) and `IsEmpty` / `IsNotEmpty` (text fields only). `IsEmpty` matches both NULL and empty string `""`, while `IsNotEmpty` excludes both. In PascalCase form: `"operator": "IsNull"`, `"IsNotNull"`, `"IsEmpty"`, `"IsNotEmpty"`. In SCREAMING_SNAKE form: `BINARY_OPERATOR_IS_NULL`, `BINARY_OPERATOR_IS_NOT_NULL`, `BINARY_OPERATOR_IS_EMPTY`, `BINARY_OPERATOR_IS_NOT_EMPTY`. ### Between operator for ranges Use `Between` (or `BINARY_OPERATOR_BETWEEN`) to filter numeric, date, or datetime fields within an inclusive range. Provide both boundaries in `rangeValues` with `lower_boundary` and `upper_boundary`. In PascalCase form (model filters): `"operator": "Between"`, `"value": "lower | upper"` (pipe-delimited string). In structured form: `"binary_operator": "BINARY_OPERATOR_BETWEEN"`, `"right_expression": { "range_values": { "lower_boundary": {...}, "upper_boundary": {...} } }`. ```json { "semanticModel": { "semanticCalculatedMeasurements": [ { "apiName": "inner_formula_dimension_example", "dataType": "Date", "expression": "SemanticAccount__dlm.created_Date", "filterLogic": "1", "filters": [ { "fieldName": "SemanticAccount__dlm.created_Date", "operator": "Between", "value": "2024-01-01T17:00:00.000-07:00 | 2023-10-10T17:00:00.000-07:00" } ] } ] } } ``` > **Performance:** Use `Between` for a range instead of separate `GreaterThanOrEqual` and `LessThanOrEqual` conditions; a single range predicate lets the engine prune partitions on date and numeric columns. ### Date and time-range filters Filter date and datetime fields using `BINARY_OPERATOR_BETWEEN` with `range_values`. Specify ISO 8601 datetime strings in `datetime_expression` for boundaries. ```json { "structuredSemanticQuery": { "fields": [ { "expression": { "table_field": { "name": "Account ID", "table_name": "ContactSemanticLayer__dll" } } }, { "expression": { "table_field": { "name": "Created Date", "table_name": "ContactSemanticLayer__dll" } } } ], "filter": { "binary_predicate": { "left_expression": { "table_field": { "name": "Created Date", "table_name": "ContactSemanticLayer__dll" } }, "binary_operator": "BINARY_OPERATOR_BETWEEN", "right_expression": { "range_values": { "upper_boundary": { "datetime_expression": "2021-12-01T00:00:00Z" }, "lower_boundary": { "datetime_expression": "2021-01-31T00:00:00Z" } } } } }, "options": { "detailed_rows": true } }, "semanticModelId": "2SMxx0000004CAeGAM" } ``` The `datetime_expression` value must be an ISO 8601 formatted timestamp that includes a time zone offset (either `Z` for UTC or a `±hh:mm` offset). Fractional seconds are optional and support up to nanosecond precision. Accepted formats include: - `2023-01-10T16:00:00.000-08:00` - `2023-01-10T16:00:00Z` - `2023-01-10T16:00:00.123456789+05:30` - `2023-01-10T00:00:00-08:00` Relative date filters compute dynamic date ranges based on an anchor date (defaulting to the current date). Use `relativeDateRange` with `datePart`, `startOffset`, and `endOffset` to define the window. Supported `datePart` values: - Time: `Minute`, `Hour`, `Day`, `Week`, `Month`, `Quarter`, `Year` - Fiscal: `Fiscal_Week`, `Fiscal_Month`, `Fiscal_Quarter`, `Fiscal_Year` - Localized: `Localized_Week` Set `useCurrentDateAsEndDate: true` to anchor the range to the current date, or provide an explicit `anchorDate` as an ISO 8601 datetime string. ```json { "structuredSemanticQuery": { "filter": { "binaryPredicate": { "leftExpression": { "tableField": { "name": "CreatedDate", "tableName": "SemanticAccount_SDO" } }, "binaryOperator": "BINARY_OPERATOR_BETWEEN", "rightExpression": { "relativeDateRange": { "datePart": "Day", "useCurrentDateAsEndDate": true, "startOffset": -30 } } } }, "fields": [ // ... ] }, "semanticModelId": "2SMxx0000004CAeGAM" } ``` ### Filter logic with AND / OR / NOT Combine multiple filters using logical operators. In structured predicate form, use `logical_binary_predicate` with `logical_binary_operator` (LOGICAL_BINARY_OPERATOR_AND, LOGICAL_BINARY_OPERATOR_OR, LOGICAL_BINARY_OPERATOR_NOT). In flatten_filter form, use a `filterLogic` string with filter indices and parentheses (e.g., `"(1 AND 2) OR 3"`). ```json { "structuredSemanticQuery": { "filter": { "logical_binary_predicate": { "left_predicate": { "binary_predicate": { "left_expression": { "table_field": { "name": "Account Name", "table_name": "AccountSemanticLayer__dll" } }, "binary_operator": "BINARY_OPERATOR_NOT_EQUAL_TO", "right_expression": { "string_expression": "Richard Schneider" } } }, "logical_binary_operator": "LOGICAL_BINARY_OPERATOR_AND", "right_predicate": { "logical_binary_predicate": { "left_predicate": { "binary_predicate": { "left_expression": { "table_field": { "name": "Annual Revenue", "table_name": "AccountSemanticLayer__dll" } }, "binary_operator": "BINARY_OPERATOR_GREATER_THAN", "right_expression": { "int_expression": 0 } } }, "logical_binary_operator": "LOGICAL_BINARY_OPERATOR_OR", "right_predicate": { "binary_predicate": { "left_expression": { "table_field": { "name": "Account Name", "table_name": "AccountSemanticLayer__dll" } }, "binary_operator": "BINARY_OPERATOR_NOT_EQUAL_TO", "right_expression": { "string_expression": "Sherry Armstrong" } } } } } } }, "fields": [ // ... ], "options": { "detailed_rows": true } }, "semanticModelId": "2SMxx0000004CAeGAM" } ``` ### Context filters Context filters apply before all other query filters (except Semantic Data Model and Semantic Data Object filters). Use `semantic_context.context_filter` (or the deprecated top-level `context_filter`) to define an independent pre-filter that all subsequent filters build upon. ```json { "structuredSemanticQuery": { "fields": [ { "expression": { "table_field": { "name": "sdm__Name__c", "table_name": "SemanticAccount__dlm" } }, "alias": "Account Name", "row_grouping": true } ], "context_filter": { "simple_dimension_filter": { "binary_predicate": { "left_expression": { "table_field": { "name": "sdm__IsActive__c", "table_name": "SemanticAccount__dlm" } }, "binary_operator": "BINARY_OPERATOR_EQUALS", "right_expression": { "bool_expression": true } } } } }, "semanticModel": { // ... } } ``` > **Performance:** Use a `context_filter` for high-selectivity baseline conditions. Context filters apply before other query filters, shrinking the working set for the whole query. ### Flatten filters Flatten filters combine multiple filter conditions using a logical expression string. Define an array of filter objects in `filters[]`, each with a PascalCase `operator`, `fieldName`, and `value` or `values`. Reference filters by 1-based index in the `filterLogic` string (e.g., `"(1 AND 2) OR (3 AND 4)"`). ```json { "structuredSemanticQuery": { "fields": [ { "expression": { "table_field": { "name": "Name", "table_name": "SemanticAccount_SDO" } }, "row_grouping": true, "alias": "account" }, { "expression": { "table_field": { "name": "MailingCity", "table_name": "SemanticContact_SDO" } }, "row_grouping": true } ], "filter": { "flattenFilter": { "filters": [ { "fieldName": "formula_measurement_example", "operator": "GreaterThan", "value": "80000" }, { "fieldName": "SemanticAccount_SDO.AnnualRevenue", "operator": "GreaterThan", "value": "90000" }, { "fieldName": "[SemanticContact_SDO].[MailingLatitude]", "operator": "GreaterThan", "value": "1", "fieldType": "FORMULA" } ], "filterLogic": "(1 AND 2) OR 3" } }, "options": { "subtotals": true, "grand_total": true } }, "semanticModel": { // ... } } ``` ### Aggregate filters (HAVING) Aggregate filters apply to post-aggregation results, equivalent to SQL HAVING clauses. Use `aggregate_filter` with a `binary_predicate` that references an aggregated expression (typically a calculated field with an aggregation function). ```json { "structuredSemanticQuery": { "fields": [ { "expression": { "table_field": { "name": "Account Name", "table_name": "AccountSemanticLayer__dll" } }, "alias": "Account Name", "row_grouping": true }, { "expression": { "table_field": { "name": "Annual Revenue", "table_name": "AccountSemanticLayer__dll" } }, "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_SUM", "alias": "Annual Revenue" } ], "aggregate_filter": { "binary_predicate": { "left_expression": { "calculated_field": { "name": "summary", "expression": "1+sum(AccountSemanticLayer__dll.AnnualRevenue__c)", "calculated_measure_expression": { "measure_output_type": "SEMANTIC_MEASUREMENT_TYPE_NUMBER" } } }, "binary_operator": "BINARY_OPERATOR_GREATER_THAN", "right_expression": { "int_expression": 5 } } } }, "semanticModelApiName": "test_model" } ``` > **Performance:** Apply row-level `filter` conditions rather than `aggregate_filter` (HAVING) whenever possible. Row-level filters reduce rows before aggregation; aggregate filters run after grouping and cannot reduce the amount of data scanned. ### Advanced dimension filters and Top-N Advanced dimension filters enable Top-N and Bottom-N queries, returning only the top or bottom N dimension values ranked by an aggregated measure. Use `advanced_dimension_filters_v2[]` with the `AdvancedDimension` operator and a `top_bottom_criteria` object specifying `top_bottom_limit`, `is_top`, and a measure `expression`. ```json { "structuredSemanticQuery": { "fields": [ { "expression": { "tableField": { "name": "sdm__Name__c", "tableName": "SemanticAccount__dlm" } }, "alias": "Account Name", "rowGrouping": true } ], "advanced_dimension_filters_v2": [ { "fields": [ { "model": "SemanticAccount__dlm.sdm__Name__c" } ], "operator": "AdvancedDimension", "top_bottom_criteria": { "top_bottom_limit": 10, "is_top": true, "expression": "sum(SemanticAccount__dlm.sdm__AnnualRevenueAmount__c)" } } ] }, "semanticModel": { // ... } } ``` > **Performance:** A filter that references an LOD expression disables the window-function optimization and forces a subquery-and-join plan, which is more expensive. ## Reference | Field (wire name) | Type | Required | Description | |---------------------------------|------------------------|----------|-------------------------------------------------------------------------------------------------| | `filter` | Predicate | N | Standard query filter; applies before aggregation. | | `context_filter` | Predicate | N | Context filter; applies before query filter (deprecated at top level; use `semantic_context.context_filter`). | | `aggregate_filter` | Predicate | N | Aggregate filter; applies to post-aggregation results (HAVING clause). | | `flatten_filter` | FlattenFilter | N | Flatten filter with logical expression indexing an array of PascalCase filter objects. | | `advanced_dimension_filters_v2` | AdvancedDimensionFilter[] | N | Top-N / Bottom-N filters on dimension values ranked by a measure. | | `binary_predicate` | BinaryPredicate | N | Binary comparison with left/right expressions and a `binary_operator`. | | `logical_binary_predicate` | LogicalBinaryPredicate | N | Combines two predicates with `logical_binary_operator` (AND / OR / NOT). | | `binary_operator` | String (enum) | Y* | Operator constant (e.g., `BINARY_OPERATOR_EQUALS`, `BINARY_OPERATOR_GREATER_THAN`). | | `operator` | String (PascalCase) | Y* | PascalCase operator in flatten_filter form (e.g., `"GreaterThan"`, `"In"`, `"StartsWith"`). | | `range_values` | RangeValues | N | Inclusive range boundaries (`lower_boundary`, `upper_boundary`) for Between operator. | | `relativeDateRange` | RelativeDateRange | N | Dynamic date range with `datePart`, `startOffset`, `endOffset`, `anchorDate`. | For the full request schema, see [Request Reference](https://developer.salesforce.com/docs/data/semantic-layer/guide/query-api-in-depth-fields.md). ## Limitations - **`IsEmpty` / `IsNotEmpty` apply to text fields only.** `IsNull` / `IsNotNull` apply to fields of any type. `IsNotEmpty` is stricter than `IsNotNull`: for a text field that can hold `""`, `IsNotNull` keeps empty-string records while `IsNotEmpty` filters them out. - **Aggregate filters (HAVING) require an aggregated expression.** The `aggregate_filter` predicate must reference a measure or calculated field with an aggregation function, not a raw dimension. ## Related - [Semantic Data Object Field](https://developer.salesforce.com/docs/data/semantic-layer/guide/connect-api-in-depth-entities.md#semantic-data-object-field-semanticdataobjectfield) — Authoring API for defining model-level filters. - [Grouping](https://developer.salesforce.com/docs/data/semantic-layer/guide/query-api-in-depth-grouping.md) — How grouping interacts with filter order-of-operations. - [Aggregation & Totals](https://developer.salesforce.com/docs/data/semantic-layer/guide/query-api-in-depth-aggregation.md) — Aggregate filters (HAVING) apply after aggregation. - [Calculated Fields](https://developer.salesforce.com/docs/data/semantic-layer/guide/query-api-in-depth-calculated-fields.md) — Use filters within calculated field expressions. - [Request Reference](https://developer.salesforce.com/docs/data/semantic-layer/guide/query-api-in-depth-fields.md) — Complete field schema for filters and predicates.