Can one metric show different numbers to different teams?

When would you use this?

Finance reviews need gross profit; weekly ops meetings need revenue - the same dashboard should serve both without anyone switching reports.
A CFO wants one governed performance metric that switches between revenue, food cost, and margin depending on the meeting.
A BI developer is tired of maintaining three nearly identical dashboards for different financial views - they want one dashboard where a filter controls which calculation runs.
Your semantic layer needs to replicate the behavior of a BI tool parameter: the user picks a value from a list, and a metric’s calculation changes accordingly.

What this recipe builds

In traditional BI tools, a parameter is a user-controlled input that can change a calculation. Users pick a value from a list, and selecting it changes which formula runs behind the scenes. Honeydew implements this through two components working together:

A disconnected parameter entity (dim_financial_view) that holds the values a BI user can filter on.
The GET_FIELD_SELECTION() function inside a metric, which reads whatever value the BI user has filtered on and routes to the correct calculation.

This recipe uses the conditional filtering concept. See the documentation for the full technical reference.

Architecture

User filters	`GET_FIELD_SELECTION` returns	Metric used
`financial_view = 'Revenue'`	`'Revenue'`	Top-line revenue
`financial_view = 'Food Cost'`	`'Food Cost'`	Ingredient cost
`financial_view = 'Gross Profit'`	`'Gross Profit'`	Revenue minus order cost
No filter	`'Gross Profit'` (default)	Revenue minus order cost

Steps

Create the parameter entity

The dim_financial_view entity is disconnected - it has no relations to any other entity. It exists purely to carry the parameter values that a BI user can filter on. Its dataset is an inline SQL VALUES statement - no warehouse table is required.

UI
YAML

Click Add new entity.
Choose Custom SQL.

Paste the following SQL:

select column1::varchar as FINANCIAL_VIEW
from VALUES('Revenue'),('Food Cost'),('Gross Profit')

Click Load columns.
Rename the entity to dim_financial_view.
Do not set any relations - continue and save.

type: entity
name: dim_financial_view
display_name: Financial View Selector
key_dataset: dim_financial_view
relations: []  # intentionally disconnected - no joins

type: dataset
entity: dim_financial_view
name: dim_financial_view
dataset_type: sql
sql: |-
  select column1::varchar as FINANCIAL_VIEW
  from VALUES('Revenue'),('Food Cost'),('Gross Profit')

Create the dynamic routing metric (revenue_dynamic)

Create a new metric called revenue_dynamic. GET_FIELD_SELECTION(entity.attribute, default) inspects the current query context and returns the value the BI user has filtered on for dim_financial_view.financial_view. If no filter is applied, it returns the default ('Gross Profit'). The CASE expression uses that value to route to the correct existing metric.

SQL
YAML

CASE GET_FIELD_SELECTION(dim_financial_view.financial_view, 'Gross Profit')
  WHEN 'Revenue'      THEN order_detail.revenue
  WHEN 'Food Cost'    THEN order_detail.order_cost
  ELSE                     order_detail.profit
END

type: metric
entity: order_detail
name: revenue_dynamic
display_name: Revenue (Dynamic)
sql: |-
  CASE GET_FIELD_SELECTION(dim_financial_view.financial_view, 'Gross Profit')
    WHEN 'Revenue'      THEN order_detail.revenue
    WHEN 'Food Cost'    THEN order_detail.order_cost
    ELSE                     order_detail.profit
  END

How the generated SQL changes

Honeydew compiles GET_FIELD_SELECTION at query time by substituting the detected filter value directly into the CASE statement:

-- No filter: resolves to Gross Profit (ELSE branch)
CASE 'Gross Profit'
  WHEN 'Revenue'      THEN sum(price)
  WHEN 'Food Cost'    THEN sum(quantity * cost_of_goods_usd)
  ELSE                     revenue - order_cost
END

-- Filter: Revenue
CASE 'Revenue'
  WHEN 'Revenue'      THEN sum(price)
  WHEN 'Food Cost'    THEN sum(quantity * cost_of_goods_usd)
  ELSE                     revenue - order_cost
END

Sample output

Same revenue_dynamic metric, 2022, top 5 cities - three different filter selections show three different numbers:

City	Gross Profit (default)	Revenue	Food Cost
Tokyo	$141M	$268M	$127M
Seoul	$140M	$268M	$128M
Delhi	$139M	$267M	$128M
London	$135M	$258M	$122M
New York City	$133M	$255M	$121M

Routing reference

Filter value	Routes to metric	Underlying SQL	Meaning
`Revenue`	`revenue`	`SUM(price)`	Top-line revenue
`Food Cost`	`order_cost`	`SUM(quantity × cost_of_goods_usd)`	Ingredient cost
`Gross Profit`	`profit`	`revenue - order_cost`	Revenue minus order cost
(no filter)	`profit`	`revenue - order_cost`	Default view

Using in a BI tool

Drag dim_financial_view.financial_view as a filter.
Select a single value: Revenue, Food Cost, or Gross Profit.
Drag order_detail.revenue_dynamic to the view. The metric uses the correct formula automatically. Switch the filter value to see the calculation change.

Key design notes

Concern	Guidance
Disconnected entity	`dim_financial_view` must have no relations to any other entity.
`GET_FIELD_SELECTION` default	The second argument is the fallback when no filter is applied. Useful when the BI tool does not support parameter defaults, or to protect against a report builder forgetting to set a value - unfiltered dashboards will still show meaningful data.
Extending to more variants	Add `WHEN` branches and corresponding metrics. The `dim_financial_view` dataset SQL just needs one more `VALUES` row.

Metrics - reference for metrics that change behavior based on user filters
Conditional filtering - deeper reference on GET_FIELD_SELECTION patterns
Entities - reference for disconnected entities

Quick Start

Semantic Ecosystem

Modeling Reference

Context Layer

Management

Integrations

Security

Release Notes

Can one metric show different numbers to different teams?

When would you use this?

What this recipe builds

Architecture

Steps

How the generated SQL changes

Sample output

Routing reference

Using in a BI tool

Key design notes

​When would you use this?

​What this recipe builds

​Architecture

​Steps

​How the generated SQL changes

​Sample output

​Routing reference

​Using in a BI tool

​Key design notes

​Related reading

When would you use this?

What this recipe builds

Architecture

Steps

How the generated SQL changes

Sample output

Routing reference

Using in a BI tool

Key design notes

Related reading