Filters
Apply permanent filters to measures for conditional aggregations.
Overview
The filters property lets you define measures that only count or aggregate rows matching specific conditions. This creates reusable filtered metrics without requiring query-time filters.
Example
measures:
- name: completed_orders
type: count
filters:
- sql: "\{CUBE\}.status = 'completed'"
- name: revenue_this_year
type: sum
sql: amount
filters:
- sql: "YEAR(\{CUBE\}.created_at) = YEAR(CURRENT_DATE)"Syntax
Single Filter
- name: active_users
type: count_distinct
sql: user_id
filters:
- sql: "\{CUBE\}.is_active = true"Multiple Filters (AND logic)
- name: completed_paid_orders
type: count
filters:
- sql: "\{CUBE\}.status = 'completed'"
- sql: "\{CUBE\}.payment_status = 'paid'"Use Cases
Percentage Calculations
measures:
- name: total_orders
type: count
- name: completed_orders
type: count
filters:
- sql: "\{CUBE\}.status = 'completed'"
- name: completion_rate
type: number
sql: "100.0 * \{completed_orders\} / NULLIF(\{total_orders\}, 0)"
format: percentTime-Based Metrics
- name: orders_last_30_days
type: count
filters:
- sql: "\{CUBE\}.created_at >= CURRENT_DATE - INTERVAL '30 days'"Best Practices
- Use {CUBE} to reference the current cube's columns
- Combine with number type for calculated ratios
- Keep filters simple — complex logic belongs in the base SQL
See Also
- cubes.measures
- cubes.measures.calculated
- cubes.segments