​
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.

​
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:
  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. 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: