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.fieldslets you specify which fields (measures, dimensions) from a joined view appear in an Explore. -
Choosing the correct
relationshipparameter. Therelationshipparameter 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.