A measure totals its own rows: the rows of the sources its formula reads. The model’s other joins decide which of those rows count. They also let a reader group or filter by the joined columns. They never multiply a measure’s rows. So a join cannot count an order twice, and a total stays right, whatever the model joins.

How it works

Each row counts once in each group, however many rows it matches. Take one order with three events: created, paid and shipped. Group Units by event type, and the order’s units count once under each of the three types. In the total, they count once.

When the groups do not add up, each group is still right. It answers its own question, such as “how many units were in orders that were shipped”. Read the total from the measure itself, never from a sum of the groups.

Joins that cannot repeat rows

Some joins cannot repeat a measure’s rows. The compiler keeps the measure’s formula as written across them:

Any other join is treated as able to repeat rows. Then the compiler works each measure out on its own rows first, and counts each row once in each group. The same is true when another model passes the measure through or evaluates it. A filter or grouping on a side that model joins applies to the rows it reaches, and never multiplies them.

The compiler trusts the cardinality you declare in a shared model. many_to_one does not remove duplicate keys. In a query, a measure the query declares is worked out on its own rows first unless the compiler proves from the sources’ SQL that the join cannot repeat them, whatever cardinality the query declares. A query is read at the grain of its groups, so a cardinality written there is easy to get wrong. Check each join on the warehouse with kater model check-join, or in the join panel of the Shared models editor. Its Measures line says how measures behave across that join. See Relationships and joins.

Examples

A many to one join adds up

semantic.shop joins each order to its customer, many to one:

1
FROM kater_dataset.orders AS o
2
-- @label Customer
3
LEFT JOIN RELATIONSHIP('customer', kater_dataset.customers AS c ON o.customer_id = c.customer_id, 'many_to_one')

Orders and Units by the customer’s loyalty tier:

1
SELECT
2
  shop."loyalty tier",
3
  shop.count_distinct__order_id,
4
  shop.sum__quantity
5
FROM workspace.shop AS shop
6
GROUP BY
7
  shop."loyalty tier"
8
ORDER BY
9
  shop."loyalty tier"

The tiers add up to 3,000 orders and 9,154 units, the shop’s totals. Each order has one customer, so it lands in one tier. The null row holds the orders of customers with no tier.

A measure of the one side counts once in each group

Customers by the region of their orders. For this example, the customers dataset also declares a sum of lifetime value, and semantic.shop passes it through:

1
  -- In kater_dataset.customers
2
  MEASURE('sum__lifetime_value', KATER__SUM(customers.lifetime_value)) AS "Lifetime Value Total"
3

4
  -- In semantic.shop
5
  c.sum__lifetime_value,

1
SELECT
2
  shop.region,
3
  shop.count_distinct__customer_id,
4
  shop.sum__lifetime_value
5
FROM workspace.shop AS shop
6
GROUP BY
7
  shop.region
8
ORDER BY
9
  shop.region

The same measures with no grouping:

1
SELECT
2
  shop.count_distinct__customer_id,
3
  shop.sum__lifetime_value
4
FROM workspace.shop AS shop

The regions add up to 976 customers, but the shop has 240. Most customers ordered in more than one region, and each counts once in every region they ordered in. Each row is right: AMER had 232 customers. The total is right too: 573,460.22 is the sum over the customers table. A plain SQL SUM over the same join counts each customer once for each order, and gives 7,209,398.55.

A one to many join counts each order once in each group

This semantic model joins each order to its events, one to many:

1
SELECT
2
  o.order_id,
3
  o.region,
4
  e.eventtype,
5
  o.sum__quantity,
6
  o.count_distinct__order_id,
7
  e.count__event_id
8
FROM kater_dataset.orders AS o
9
-- @label Events
10
LEFT JOIN RELATIONSHIP('events', kater_dataset.order_events AS e ON o.order_id = e.order_id, 'one_to_many')

kater model check-join confirms the cardinality on the warehouse:

events: 3,000 of 3,000 rows match. Each matches at most 4 rows.
The joined model has 7,436 rows and 3,000 key values.
The rows fit one to many.

A query of its workspace, activity, groups Units, Orders and Events by event type:

1
SELECT
2
  activity.eventtype,
3
  activity.sum__quantity,
4
  activity.count_distinct__order_id,
5
  activity.count__event_id
6
FROM workspace.activity AS activity
7
GROUP BY
8
  activity.eventtype
9
ORDER BY
10
  activity.eventtype

The same measures with no grouping:

1
SELECT
2
  activity.sum__quantity,
3
  activity.count_distinct__order_id,
4
  activity.count__event_id
5
FROM workspace.activity AS activity

Every order has a created event, so the created row holds every order. An order that was also paid and shipped counts under those types too. So the Units rows add up to 22,729, and the total stays 9,154. A plain SQL SUM of the quantity over the same join gives 22,729 for the total. It adds each order once for each event.

A measure that reads both sides of a repeating join

Orders per Customer, declared in semantic.shop:

1
  MEASURE('orders_per_customer', EVAL(o.count_distinct__order_id) / NULLIF(EVAL(c.count_distinct__customer_id), 0)) AS "Orders per Customer",

The compiler refuses the model with unsupported_measure_context: “orders_per_customer evaluates measures that read different sources, and this model’s joins repeat one’s rows for the other. Declare it in a model where they read one source, or evaluate each in the query that needs it.”

The join is many to one toward customers, so it repeats each customer’s row once for each order. A formula that reads both sides in this model would read those copies. Declare the measure in a query of the workspace, where each measure is worked out on its own rows first:

1
SELECT
2
  shop."loyalty tier",
3
  shop.count_distinct__order_id,
4
  shop.count_distinct__customer_id,
5
  -- @format number(1)
6
  MEASURE(
7
    'orders_per_customer',
8
    EVAL(shop.count_distinct__order_id) / NULLIF(EVAL(shop.count_distinct__customer_id), 0)
9
  ) AS "Orders per Customer"
10
FROM workspace.shop AS shop
11
GROUP BY
12
  shop."loyalty tier"
13
ORDER BY
14
  shop."loyalty tier"

Each tier’s ratio divides its own two totals. The same holds for Events per Order on the activity model: the model refuses it, and a query of its workspace gives AMER 2.41.

Several row sets in one model

A model can hold several row sets as UNION ALL branches, such as web orders and store orders. Read the union through a SELECT * CTE, and declare each dimension. A merged dimension is a new field, so it needs a new name. That is true even when every branch reads a field of that name. DIMENSION('region', base.region) is refused, because region is already a dimension of kater_dataset.orders.

This semantic model, channel_units, stacks the shop’s web orders and store orders. The store branch writes NULL for the campaign:

1
WITH unioned AS (
2
  SELECT o.region, o.order_month, o.campaign, o.quantity AS web_units, CAST(NULL AS INTEGER) AS store_units
3
  FROM kater_dataset.orders AS o
4
  WHERE o.channel = 'web'
5
  UNION ALL
6
  SELECT o.region, o.order_month, CAST(NULL AS VARCHAR) AS campaign, NULL AS web_units, o.quantity AS store_units
7
  FROM kater_dataset.orders AS o
8
  WHERE o.channel = 'store'
9
), base AS (
10
  SELECT * FROM unioned
11
)
12
SELECT
13
  DIMENSION('union__region', base.region) AS "Region",
14
  DIMENSION('union__order_month', base.order_month) AS "Order Month",
15
  DIMENSION('union__campaign', base.campaign) AS "Campaign",
16
  MEASURE('web_units', SUM(base.web_units)) AS "Web Units",
17
  MEASURE('store_units', SUM(base.store_units)) AS "Store Units"
18
FROM base

Each measure totals the branches its aggregate reads. Web Units totals the web rows, and Store Units totals the store rows. The row sets meet on the dimensions they share. A branch that writes NULL for a dimension, directly or through a cast, does not carry it. The compiler works all of this out from the SQL. Nothing is declared.

Two row sets meet on the dimensions they share

Web Units and Store Units by region, from the workspace channels that reads channel_units.

1
SELECT
2
  channels.union__region,
3
  channels.web_units,
4
  channels.store_units
5
FROM workspace.channels AS channels
6
GROUP BY
7
  channels.union__region
8
ORDER BY
9
  channels.union__region

Both branches carry the region, so each measure totals its own rows for each region. The columns add up to 2,149 web units and 2,330 store units.

A measure cannot be grouped by a dimension its rows do not have

Store Units by campaign.

1
SELECT
2
  channels.union__campaign,
3
  EVAL(channels.store_units)
4
FROM workspace.channels AS channels
5
GROUP BY
6
  channels.union__campaign

The compiler refuses it with dimension_outside_measure_rows: “Store Units can’t be grouped by Campaign. Some of the rows it totals have no Campaign, so they would all count toward an empty Campaign. Remove Campaign from the GROUP BY, or write EVAL(store_units, REMOVE(union__campaign)) to total Store Units without it.”

A filter on the campaign, such as WHERE channels.union__campaign = 'spring', is refused with the same code: “Store Units can’t be filtered by Campaign. Some of the rows it totals have no Campaign, so the filter would drop them all.”

Total a measure without that dimension

Web Units by campaign, beside all Store Units.

1
SELECT
2
  channels.union__campaign,
3
  EVAL(channels.web_units),
4
  EVAL(channels.store_units, REMOVE(channels.union__campaign))
5
FROM workspace.channels AS channels
6
GROUP BY
7
  channels.union__campaign
8
ORDER BY
9
  channels.union__campaign

REMOVE takes the campaign out of the grouping for Store Units only, so every row shows all 2,330 store units. Web Units keeps the grouping. The null row is the store rows, which have no campaign, so it has no web units. See Change a measure’s context for REMOVE.

Where you see it

A measure’s definition card lists, under Inputs, the dimensions a reader can group or filter it by. A dimension outside the measure’s rows is left out there and listed under Not allowed. Open the Definitions view, or run kater model explain store_units --relation semantic.channel_units:

The workspace keeps the limit when it passes Store Units on, so a query of channels meets it too.

Rules

• A measure totals its own rows. The rows are those of the sources its formula reads, and nothing else.
• Joins never multiply a measure’s rows. Each row counts once in each group, however many rows it matches.
• Groups from a repeating join do not add up. Each group is right. Read the total from the measure, not from a sum of the groups.
• A declared cardinality changes no data. The compiler trusts it. Check each join on the warehouse.
• A formula reads one set of rows. A measure that evaluates two sources across a repeating join is refused. Declare it in a query of the model.
• A plain aggregate has no such guard. A plain SQL SUM over a one to many join counts each row once for each match. Use a measure.
• Each union branch carries only the dimensions it writes. A measure cannot be grouped or filtered by a dimension that its branch writes as NULL.

Common errors