Filters and Operators

For Data Analysts

Filters allow you to narrow down query results to specific subsets of data. This page covers all available filter operators and how to combine them effectively.

Filter Structure

Each filter consists of three parts:

{
  "member": "cube.dimension",
  "operator": "operatorName",
  "values": ["value1", "value2"]
}

Field	Description
member	The dimension or measure to filter on
operator	The comparison operation to perform
values	Array of values to compare against

Equality Operators

equals

Match exact values. Multiple values act as OR conditions.

{
  "member": "orders.status",
  "operator": "equals",
  "values": ["completed"]
}

Multiple values:

{
  "member": "orders.region",
  "operator": "equals",
  "values": ["NORTH", "SOUTH"]
}

Generated SQL: region IN ('NORTH', 'SOUTH')

notEquals

Exclude specific values.

{
  "member": "orders.status",
  "operator": "notEquals",
  "values": ["cancelled"]
}

Multiple values:

{
  "member": "orders.status",
  "operator": "notEquals",
  "values": ["cancelled", "refunded"]
}

Generated SQL: status NOT IN ('cancelled', 'refunded')

Comparison Operators

gt (Greater Than)

Values strictly greater than the specified value.

{
  "member": "orders.total_amount",
  "operator": "gt",
  "values": [1000]
}

Generated SQL: total_amount > 1000

gte (Greater Than or Equal)

Values greater than or equal to the specified value.

{
  "member": "orders.order_date",
  "operator": "gte",
  "values": ["2024-01-01"]
}

Generated SQL: order_date >= '2024-01-01'

lt (Less Than)

Values strictly less than the specified value.

{
  "member": "orders.quantity",
  "operator": "lt",
  "values": [100]
}

Generated SQL: quantity < 100

lte (Less Than or Equal)

Values less than or equal to the specified value.

{
  "member": "orders.discount_percent",
  "operator": "lte",
  "values": [25]
}

Generated SQL: discount_percent <= 25

String Operators

contains

Match strings containing the specified substring. Case-sensitive by default.

{
  "member": "customers.email",
  "operator": "contains",
  "values": ["@company.com"]
}

Generated SQL: email LIKE '%@company.com%'

notContains

Exclude strings containing the specified substring.

{
  "member": "products.name",
  "operator": "notContains",
  "values": ["discontinued"]
}

Generated SQL: name NOT LIKE '%discontinued%'

startsWith

Match strings that begin with the specified prefix.

{
  "member": "orders.order_id",
  "operator": "startsWith",
  "values": ["ORD-2024"]
}

Generated SQL: order_id LIKE 'ORD-2024%'

endsWith

Match strings that end with the specified suffix.

{
  "member": "products.sku",
  "operator": "endsWith",
  "values": ["-XL"]
}

Generated SQL: sku LIKE '%-XL'

List Operators

inList

Match any value in the provided list.

{
  "member": "orders.region",
  "operator": "inList",
  "values": ["NORTH", "SOUTH", "EAST"]
}

Generated SQL: region IN ('NORTH', 'SOUTH', 'EAST')

notInList

Exclude values in the provided list.

{
  "member": "orders.payment_method",
  "operator": "notInList",
  "values": ["cash", "check"]
}

Generated SQL: payment_method NOT IN ('cash', 'check')

Date Range Operators

inDateRange

Filter to dates within a range (inclusive).

{
  "member": "orders.order_date",
  "operator": "inDateRange",
  "values": ["2024-01-01", "2024-03-31"]
}

Generated SQL: order_date BETWEEN '2024-01-01' AND '2024-03-31'

notInDateRange

Exclude dates within a range.

{
  "member": "orders.order_date",
  "operator": "notInDateRange",
  "values": ["2024-02-01", "2024-02-29"]
}

Generated SQL: NOT (order_date BETWEEN '2024-02-01' AND '2024-02-29')

beforeDate

Filter to dates before the specified date.

{
  "member": "orders.order_date",
  "operator": "beforeDate",
  "values": ["2024-01-01"]
}

Generated SQL: order_date < '2024-01-01'

afterDate

Filter to dates after the specified date.

{
  "member": "orders.order_date",
  "operator": "afterDate",
  "values": ["2024-12-31"]
}

Generated SQL: order_date > '2024-12-31'

Null Operators

set

Filter to non-null values.

{
  "member": "orders.shipped_date",
  "operator": "set",
  "values": []
}

Generated SQL: shipped_date IS NOT NULL

notSet

Filter to null values.

{
  "member": "orders.cancelled_at",
  "operator": "notSet",
  "values": []
}

Generated SQL: cancelled_at IS NULL

Combining Filters

Multiple Filters (AND)

All filters in the array are combined with AND logic.

{
  "measures": ["orders.total_revenue"],
  "dimensions": ["orders.region"],
  "filters": [
    {
      "member": "orders.status",
      "operator": "equals",
      "values": ["completed"]
    },
    {
      "member": "orders.total_amount",
      "operator": "gte",
      "values": [100]
    },
    {
      "member": "orders.order_date",
      "operator": "inDateRange",
      "values": ["2024-01-01", "2024-12-31"]
    }
  ]
}

Generated WHERE clause:

WHERE status = 'completed'
  AND total_amount >= 100
  AND order_date BETWEEN '2024-01-01' AND '2024-12-31'

OR Logic with Equals

Use multiple values with equals for OR within a single field.

{
  "member": "orders.status",
  "operator": "equals",
  "values": ["completed", "shipped", "delivered"]
}

Generated SQL: status IN ('completed', 'shipped', 'delivered')

Operator Reference by Data Type

String Dimensions

Operator	Description
equals	Exact match
notEquals	Not equal
contains	Substring match
notContains	Exclude substring
startsWith	Prefix match
endsWith	Suffix match
inList	Match any in list
notInList	Exclude all in list
set	Not null
notSet	Is null

Numeric Dimensions

Operator	Description
equals	Exact match
notEquals	Not equal
gt	Greater than
gte	Greater than or equal
lt	Less than
lte	Less than or equal
inList	Match any in list
notInList	Exclude all in list
set	Not null
notSet	Is null

Time Dimensions

Operator	Description
equals	Exact date/time
notEquals	Not equal
gt	After (exclusive)
gte	On or after
lt	Before (exclusive)
lte	On or before
inDateRange	Within range
notInDateRange	Outside range
beforeDate	Before date
afterDate	After date
set	Not null
notSet	Is null

Boolean Dimensions

Operator	Description
equals	Match true/false
notEquals	Not equal
set	Not null
notSet	Is null

Filtering on Measures

You can also filter on aggregated measure values using the having clause logic.

{
  "measures": ["orders.total_revenue", "orders.order_count"],
  "dimensions": ["orders.customer_id"],
  "filters": [
    {
      "member": "orders.order_count",
      "operator": "gte",
      "values": [10]
    }
  ]
}

This returns only customers with 10 or more orders.

Practical Examples

High-Value Completed Orders

{
  "measures": ["orders.total_revenue"],
  "dimensions": ["orders.customer_id", "orders.region"],
  "filters": [
    {
      "member": "orders.status",
      "operator": "equals",
      "values": ["completed"]
    },
    {
      "member": "orders.total_amount",
      "operator": "gt",
      "values": [5000]
    }
  ]
}

Orders from Specific Regions Excluding Returns

{
  "measures": ["orders.order_count"],
  "dimensions": ["orders.region", "orders.order_date.month"],
  "filters": [
    {
      "member": "orders.region",
      "operator": "inList",
      "values": ["NORTH", "SOUTH", "EAST"]
    },
    {
      "member": "orders.status",
      "operator": "notInList",
      "values": ["returned", "refunded"]
    }
  ]
}

Recent Orders with Tracking

{
  "measures": ["orders.order_count"],
  "dimensions": ["orders.shipping_carrier"],
  "filters": [
    {
      "member": "orders.order_date",
      "operator": "inDateRange",
      "values": ["2024-01-01", "2024-01-31"]
    },
    {
      "member": "orders.tracking_number",
      "operator": "set",
      "values": []
    }
  ]
}

Customer Email Domain Analysis

{
  "measures": ["customers.count"],
  "dimensions": ["customers.segment"],
  "filters": [
    {
      "member": "customers.email",
      "operator": "contains",
      "values": ["@enterprise.com"]
    }
  ]
}

Orders Within Price Range

{
  "measures": ["orders.order_count", "orders.total_revenue"],
  "dimensions": ["orders.product_category"],
  "filters": [
    {
      "member": "orders.unit_price",
      "operator": "gte",
      "values": [50]
    },
    {
      "member": "orders.unit_price",
      "operator": "lte",
      "values": [200]
    }
  ]
}

Filter Performance Tips

1. Filter on indexed columns - Apply filters on dimensions that are indexed in your warehouse
2. Use inList for multiple values - More efficient than multiple equals filters
3. Filter early - Filters reduce data volume before aggregation
4. Avoid contains on large tables - contains triggers full table scans
5. Use date ranges - Time-based filters often leverage partitioning

Error Handling

Invalid Operator for Data Type

{
  "error": {
    "code": "INVALID_FILTER",
    "message": "Operator 'contains' is not valid for dimension type 'number'",
    "dimension": "orders.total_amount",
    "dimension_type": "number",
    "valid_operators": ["equals", "notEquals", "gt", "gte", "lt", "lte", "inList", "notInList", "set", "notSet"]
  }
}

Missing Required Values

{
  "error": {
    "code": "INVALID_FILTER",
    "message": "Operator 'inDateRange' requires exactly 2 values [start, end]",
    "provided_values": 1
  }
}

Next Steps

• Learn about query optimization
• Explore time intelligence
• Define segments in cubes