Question

A Looker developer builds an Explore that joins an orders table (one row per order) to an order_items table (many rows per order) using a one-to-many relationship. Business users report that a measure summing order_amount (defined on the orders view) returns inflated totals whenever they also include fields from order_items in the same query. What is the correct way to define the model so this measure returns accurate results regardless of the join fan-out?

Accepted Answer

Define order_amount as a type: sum_distinct measure with a sql_distinct_key set to the orders primary key, and ensure the join specifies the correct relationship (one_to_many).

Answer

Change the join to a many_to_one relationship so Looker automatically deduplicates the orders rows before aggregating.

Answer

Move the order_amount measure into the order_items view and sum it there so each item row is counted individually.

Answer

Add a filtered measure with a WHERE clause that excludes duplicate order rows created by the join.