When I first started at Omni, one of my early support tickets was a customer creating a parameter that let them dynamically adjust a threshold, and a calculated field that recalculated based on whatever value was in that parameter. Something like “show me the count of orders that had at least N items in them,” where N was a number the viewer could change on the fly.
My first thought was to apply a filter directly to the calculated field. That didn’t work; the results stayed exactly the same no matter what value I filtered on. The reason turned out to be that the field was a measure, and measures get computed after Omni groups and aggregates the rows. Filtering on a value that’s only calculated after grouping doesn’t change anything. Converting the field to a dimension calculates it per row, before any grouping happens, which fixed that specific problem.
Once the field worked correctly, applying a filter to it affected the whole workbook tab. What the customer actually wanted was for one specific measure to respond to that threshold, while everything else in the workbook ignored it entirely. A regular filter can’t do that; it’s not scoped to a single field.
The answer was templated filters, a way to build a filter’s value directly into a single’s field SQL, and I remember spending a while on it and not quite landing on the pattern at first. Here’s the version I wish I’d had in front of me at the time.
Example
Say you’re an E-commerce store with an orders view and a dimension called total_items_per_order. You want a measure called high_volume_order_count that only counts orders that meet a minimum item count and you want that minimum to be adjustable from a filter.
1. Define a filter-only field in the view
filters:
min_order_item_count:
type: number
label: "Min Items Per Order"
description: Templated filter for high_volume_order_count
The field doesn’t pull from a column, it exists so that people have something to set a value in the filter bar. It shows up in the field picker.
2. Reference it from the measure, using mustache syntax
name: high_volume_order_count
aggregate_type: count_distinct
sql: |-
CASE
WHEN {{# orders.min_order_item_count.filter }} # {{#}} opens the section
${orders.total_items_per_order} # ${.} field reference
{{/ orders.min_order_item_count.filter }} # {{/}} closes the section
{{^ orders.min_order_item_count.filter }} # {{^}} starts the inverse
TRUE
{{/ orders.min_order_item_count.filter }}
THEN ${orders.order_id}
END
If someone sets min_order_item_count to >= 3, Omni fills in total_items_per_order as the left side of the comparison and the filter’s own operator and chosen number as the right side. The SQL becomes CASE WHEN total_items_per_order >= 3 THEN order_id END . An order with 5 items passes through and gets counted, an order with 1 item becomes NULL and doesn’t. If nobody’s set the filter, that whole comparison is replaced with TRUE, so the SQL becomes CASE WHEN TRUE THEN order_id END and every order counts.
In summary, define the filter as a placeholder field, then let its presence or absence decide what the SQL becomes. It scopes to exactly the one measure you attached it to, with everything else in the workbook left alone.