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 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

{
  "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)

{
  "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.

{
  "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": {...} } }.

{
  "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.

{
  "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.

{
  "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").

{
  "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.

{
  "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)").

{
  "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).

{
  "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.

{
  "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.

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.

Semantic Data Object Field — Authoring API for defining model-level filters.
Grouping — How grouping interacts with filter order-of-operations.
Aggregation & Totals — Aggregate filters (HAVING) apply after aggregation.
Calculated Fields — Use filters within calculated field expressions.
Request Reference — Complete field schema for filters and predicates.