The Question:

I have defined some measures in a view. I now join this view to another view and I don't see the measures anymore, only the dimensions. Where are my measures?

An Answer:

Symmetric aggregates let measures of all types be brought through joins. However, this is only possible if there are primary keys defined in the views on both sides of the join. In order to have measures come through joins, you will want to define a primary key in all views involved.

You can do this by adding primary_key: yes to the primary key field definition, like so:

dimension: id {
 type: number
 primary_key: yes
}

Note: Primary keys must have completely unique, non-NULL values to be used with joined sums and averages. If your primary key is not unique, you might see 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, but a combination of fields is unique, we can make a compound primary key by concatenating together two or more fields in SQL. Read more about how to resolve this error in our Help Center article on Dealing with Non-Unique Primary Keys.

Without Symmetric Aggregates

Some dialects do not support symmetric aggregates. When symmetric aggregates cannot be used, Looker will only bring the count 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 our full blog post about aggregate functions gone bad.

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

Consider:

• The use of fields (for Joins) at the join level. Fields enable you to specify which fields (measures, dimensions) from a joined view are brought into an Explore.
• Choosing the correct relationship parameter. The relationship parameter tells Looker whether to invoke symmetric aggregates when the join is written into a SQL query. It's important to properly define the type of relationship in order for Looker to calculate accurate measures.