Conditional Filtering

Goal

Conditional Filtering lets the semantic layer detect and respond to filters coming from a BI tool or an AI agent, so that metric logic can adapt dynamically to the user’s query context. So, instead of blindly applying the same transformation or metric calculation every time, the semantic logic can take into consideration the filters the user provided (like region = Europe, or date >= 2024-01-01). That allows to create default data filtering, and to adjust business logic based on user intent.

Syntax

GET_FIELD_SELECTION(entity.field, [default value expression])

Parameters:

Single field (entity.field) to detect user filters on
Default value expression in case no user filters on entity.field were provided. If not set, the default value is NULL.

Returns:

The filter value if the BI or AI query filtered entity.filter to a single value, i.e. For the user filter entity.field = 'value', then would return value
The default value if there was no equality filter.

Only equality filters are detected, as sent by most supported BI tools.

Honeydew uses Conjunctive Normal Form (CNF) decomposition to detect equality filters even when they’re embedded in complex expressions.

For example, given the expression GET_FIELD_SELECTION(entity.field) and a user filter - The following examples would all return value given the filter for it:

entity.field = 'value'
entity.field IN ('value')
entity.field = 'value' AND entity.other_field = 'other_value'

However, multiple values, expressions or compound (OR) filters are not detected. The following examples would all return NULL (or the default value if set):

entity.field || '' = 'value'
entity.field IN ('value', 'other')
entity.field = 'value' OR entity.other_field = 'value'
entity.field >= 'a'

Using GET_FIELD_SELECTION with Power BIPower BI filtering is case insensitive. To support Power BI, always use same (lower or upper) case values.

Multiple Fallback Tiers

To fall back across several fields, wrap separate GET_FIELD_SELECTION calls in COALESCE. With no default value a call returns NULL, so the leftmost field the user filtered wins:

-- A direct filter on the reference date wins; else a BI selector entity; else today
dim_date.date = COALESCE(
    GET_FIELD_SELECTION(dim_date.date),
    GET_FIELD_SELECTION(dim_date_choice.date),
    CURRENT_DATE)

A GET_FIELD_SELECTION cannot be nested inside another one’s default value expression.

A default value hides the difference between “no filter” and “a filter I could not detect”. Since only equality filters are detected, a query filtering the field to several values or to a range gets the default, and the data is reduced as if the user had asked for nothing.Where that matters, give the user a way to widen the filter: a two-value scope selector on a disconnected entity, with the wide branch returning TRUE, as in performance filtering.

Detect filters on the same field the expression constrains, as dim_date.date does in the first tier above.Detecting only a different field leaves a direct filter on the constrained field undetected: the expression resolves to a later tier and is then combined with the user’s own filter, returning the intersection of both. See ensuring filtering for a point in time for this pattern applied to versioned data.

Use Cases

Change Calculation Based on User Intent

Use the filter a user has supplied to change how a metric is calculated. This is most commonly used to create different metric is calculations at different grains, over the same data. Examples: Dynamic cohorts Given a dim_cohort defining user cohorts, simulate based on cohort choice

sales.total_sales_in_cohort =
    sales.total_sales FILTER (WHERE user.cohort = GET_FIELD_SELECTION(dim_cohort.cohort_choice))

Per grain logic Given multiple grains and dim_grain selector entity, choose how a metric is calculated at each grain (See Metrics with different grains).

daily_data.total_revenue =
  CASE GET_FIELD_SELECTION(dim_grain.grain)
    -- with daily grain simple sum up the revenue
    WHEN `daily` THEN SUM(daily_data.revenue)
    -- with monthly grain, apply adjustments recorded at end of month
    WHEN `monthly` THEN SUM(daily_data.revenue) - SUM(monthly_adjustments.revenue_adjustment)
  END

Simulate data based on user selection

-- Calculate sales based on chosen tax level
-- Would return different values for different filters the user chooses
sales.total_sales_simulated_tax =
    SUM(sales.sales_pre_tax * GET_FIELD_SELECTION(dim_tax_level.tax_level))

Most business logic does not care about user filters, and is only based on the data.For example when want to calculate differently for 2023 and 2024, then whether the user chose 2023 or 2024 does not matter. How to calculate is a property of the data, not what the user wanted:

-- Calculate sales. Accounts for change in tax between 2023/2024
-- Is the same regardless to user filter
sales.total_sales =
    SUM(sales.sales_pre_tax *
        CASE
            WHEN sales.sale_year = 2023 THEN 1.17
            WHEN sales.sale_year = 2024 THEN 1.18
        END
    )

However, when exactly the same data is differently processed based on user intent then must look at user filters, as in the total_sales_simulated_tax example above.

Default Filtering

Apply a filter on the data by default, unless a user has instructed otherwise. This is used for:

Reduce Cost & Improve Performance with BI: when users build new BI reports, they would typically send unfiltered queries looking at all the data. They usually don’t want to aggregate all the data, but they may have dragged a metric on a dashboard first and a date filter only after. Default filtering enables to apply a filter on the data until the user decided to change it. For example, force a “last 2 weeks” filter on the big fact tables up until the user explicitly asked for more.
Ensure Correctness By Removing Duplicated Data: in some data modeling scenarios, data may have multiple versions recorded. Aggregating on the data would result in double counting and a wrong result. Default filtering enables to enforce choosing a specific version of the data, always ensuring there is no double counting.

Default filtering is typically implemented using GET_FIELD_SELECTION with Domain Source Filters. For example, add a source filter such as

fact_sales.date >=
    CASE GET_FIELD_SELECTION(dim_data_cutoff.cutoff, 'week')
        WHEN 'week'  THEN CURRENT_DATE - INTERVAL '7' DAY
        WHEN 'month' THEN CURRENT_DATE - INTERVAL '1' MONTH
        WHEN 'year'  THEN CURRENT_DATE - INTERVAL '1' YEAR
        ELSE              DATE '1900-01-01'
    END

See for example:

Default filter for performance filtering
Default filter for multi-grain data.
Default filter for slowly changing dimensions

Quick Start

Semantic Ecosystem

Modeling Reference

Context Layer

Management

Integrations

Security

Release Notes

Goal

Syntax

Multiple Fallback Tiers

Use Cases

Change Calculation Based on User Intent

Default Filtering

​Goal

​Syntax

​Multiple Fallback Tiers

​Use Cases

​Change Calculation Based on User Intent

​Default Filtering

Goal

Syntax

Multiple Fallback Tiers

Use Cases

Change Calculation Based on User Intent

Default Filtering