> ## Documentation Index
> Fetch the complete documentation index at: https://honeydew.ai/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# 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

```sql theme={null}
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](/docs/integration/bi-tools/supported-tools).

<Tip>
  Honeydew uses Conjunctive Normal Form (CNF) decomposition to detect equality filters even when they're embedded in complex expressions.
</Tip>

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'`

<Warning>
  **Using GET\_FIELD\_SELECTION with Power BI**

  Power BI filtering is [case insensitive](/docs/integration/bi-tools/powerbi#case-sensitive-data). To support Power BI, always use same (lower or upper) case values.
</Warning>

### 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:

```sql theme={null}
-- 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)
```

<Warning>
  A `GET_FIELD_SELECTION` cannot be nested inside another one's default value expression.
</Warning>

<Warning>
  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](/docs/performance/filtering-and-sampling#force-performance-filters-with-conditional-filtering).
</Warning>

<Tip>
  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](/docs/advanced-modeling/slowly-changing-dimensions#ensuring-filtering-for-a-point-in-time)
  for this pattern applied to versioned data.
</Tip>

## 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

```sql theme={null}
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](/docs/calculations/metrics#different-logic-for-different-grains)).

```sql theme={null}
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**

```sql theme={null}
-- 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))
```

<Tip>
  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*:

  ```sql theme={null}
  -- 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.
</Tip>

### Default Filtering

Apply a filter on the data by default, unless a user has instructed otherwise.

This is used for:

1. **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.

2. **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](/docs/domains#source-filters).

For example, add a source filter such as

```sql theme={null}
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](/docs/performance/filtering-and-sampling#force-performance-filters-with-conditional-filtering)
* Default filter for [multi-grain data](/docs/advanced-modeling/multi-grain-tables#domain-level-deduplication).
* Default filter for [slowly changing dimensions](/docs/advanced-modeling/slowly-changing-dimensions#ensuring-filtering-for-a-point-in-time)