> ## 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.
# Can one metric show different numbers to different teams?
> Route a single metric to different calculations based on a BI user's filter selection
## 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:
1. A **disconnected parameter entity** (`dim_financial_view`) that holds the
values a BI user can filter on.
2. 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](/docs/advanced-modeling/conditional-filtering) concept.
See the documentation for the full technical reference.
## Architecture
```mermaid theme={null}
flowchart LR
param(["dim_financial_view
(Revenue · Food Cost · Gross Profit)"])
dynamic(["revenue_dynamic
(GET_FIELD_SELECTION)"])
rev(["revenue
(SUM(price))"])
fc(["order_cost
(SUM(quantity × cost_of_goods_usd))"])
profit(["profit
(revenue - order_cost)"])
param -->|"filter selection"| dynamic
dynamic -->|"Revenue"| rev
dynamic -->|"Food Cost"| fc
dynamic -->|"Gross Profit (default)"| profit
```
| 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
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.
1. Click **Add new entity**.
2. Choose **Custom SQL**.
3. Paste the following SQL:
```sql theme={null}
select column1::varchar as FINANCIAL_VIEW
from VALUES('Revenue'),('Food Cost'),('Gross Profit')
```
4. Click **Load columns**.
5. Rename the entity to `dim_financial_view`.
6. Do not set any relations - continue and save.
```yaml theme={null}
type: entity
name: dim_financial_view
display_name: Financial View Selector
key_dataset: dim_financial_view
relations: [] # intentionally disconnected - no joins
```
```yaml theme={null}
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 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 theme={null}
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
```
```yaml theme={null}
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:
```sql theme={null}
-- 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
1. Drag `dim_financial_view.financial_view` as a filter.
2. Select a single value: `Revenue`, `Food Cost`, or `Gross Profit`.
3. 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. |
## Related reading
* [Metrics](/docs/calculations/metrics#metric-computed-differently-based-on-user-filters) -
reference for metrics that change behavior based on user filters
* [Conditional filtering](/docs/advanced-modeling/conditional-filtering) - deeper reference
on `GET_FIELD_SELECTION` patterns
* [Entities](/docs/modeling/entities) - reference for disconnected entities