How can I add a filter on just one column / measure? (filtered measure)

Workbooks
,
1

To apply a filter to one but not all measures in a given query, you can use a filtered measure.

There are two easy ways to create a filtered measure from the UI:

  1. Create a query with the measure you’d like to filter, and then pivot on the dimension containing the value you want to filter, then use the quick “Create Filtered measure” menu item
    create_filtered_measure_quick_agg

  2. Create a new measure and add a filter to it by Editing the Field. You can create a new measure either from a the quick Aggregation menu on a dimension (shown in the gif below), or by duplicating an existing measure from the menu in the field picker.
    filtered_measure_duplicate

If you would prefer to do this from the IDE, you can also create a filtered measure in code. For example:

  order_items_count_status_complete:
    aggregate_type: count
    filters:
      status:
        is: Complete

  margin_sum:
    sql: ${order_items.margin}
    label: Completed Order Margin Sum
    aggregate_type: sum
    filters:
      status:
        is: Complete

For more, check out the filtered measure documentation.

If you’re more of a SQL Person, you’ll notice that filtered measures are essentially equivalent to writing something like COUNT(DISTINCT CASE WHEN status = 'Complete' THEN order_id ELSE NULL END, but has the added benefit of intelligently passing through the filter value when you drill.