Why aren't my measures coming through a join?

You have defined some measures in a view — but when you join that view to another view you no longer see the measures in an Explore. Where did the measures go?

Possible causes and solutions

The possible underlying causes can depend on whether you have symmetric aggregates enabled. Symmetric aggregates are automatically enabled unless your developer has disabled them with the symmetric_aggregates: no LookML setting or if your database dialect does not support symmetric aggregates.

When symmetric aggregates are enabled

Symmetric aggregates let measures of all types carry through joins. However, this is only possible if there are primary keys defined in the views on both sides of a join. To enable measures to carry through joins, you need to define a primary key in all involved views.

You can define a primary key in each view by adding primary_key: yes to the primary key field definition, like so:

dimension: id {
 type: number
 primary_key: yes
}

Primary keys must have completely unique, non-NULL values for measure types sum and average to carry through joins. If your primary key is not unique, you might receive an error like the following:

Non-Unique value/primary key (or sql_distinct_key), value overflow or collision when computing sum.

Additionally, if you are in a situation where there is not a single unique field in your view, you can make a compound primary key by concatenating together two or more fields in SQL to create a unique combination of values. Read more about how to resolve this error in the Non-unique primary key section of the Error: Non-Unique value/primary key (or sql_distinct_key), value overflow or collision when computing sum Best Practices page.

When symmetric aggregates are not enabled

Some dialects do not support symmetric aggregates, or your Looker developer has disabled symmetric aggregates with the symmetric_aggregates: no LookML setting. When symmetric aggregates cannot be used, Looker will only carry count type measures (which become count distinct measures) through a join by default. This approach also requires a primary key, because the count distinct will use the primary key. This prevents inaccurate results and fanouts. Check out the Community post about aggregate functions gone bad.

There is a list that shows which dialects support symmetric aggregates in the most recent Looker release.

Additional solutions to consider

• The use of fields (for joins) at the join level. fields lets you specify which fields (measures, dimensions) from a joined view appear in an Explore.
• Choosing the correct relationship parameter. The relationship parameter tells Looker whether to apply symmetric aggregates when the join is used in a SQL query. It's important to properly define the type of relationship to allow Looker to calculate accurate measure values.