join_typeshould be avoided as of release 3.10
join_type allows you to describe a
join as having a 1-to-1 relationship.
Join relationships that are not 1-to-1 can create inaccurate results when aggregate functions are used. Since Looker
measures are in fact aggregate functions, only
type: count measures (as
COUNT DISTINCT) are brought from joined views into the explore. Consider this example:
In this situation, when a user works with the order explore, they will see all the dimensions and measures from order. They will also see all the dimensions from customer, but will only see the
type: count measures from customer. Looker works this way so that repeated rows, which can occur in many-to-1 and 1-to-many joins, do not inflate values in functions such as
SUM. This topic is discussed in more detail in this blog post.
Order to customer is a many-to-1 relationship. If it was a 1-to-1 relationship instead, you could use
join_type to force the non-count measures from customer to be included.
Declare the join of person to dna as having a 1-to-1 relationship
join_type Should Not Be Used Unless The Join Is Actually 1-to-1
Many users are tempted to use
join_type: one_to_one, even if the join is many-to-1 or 1-to-many, solely to expose all measures in an explore. We strongly suggest you do not do this, because in certain situations, measures will not give you accurate results. Rows are often repeated in many-to-1 or 1-to-many joins, causing double counting and similar errors. This blog post explains this issue in more detail.
Things to Know
You Can Expose Measures From Joined Views Without Using
In some cases you might carefully consider the situation, and decide that it is safe to add a non-count measure from a joined view to an explore. There is a method to accomplish this. Suppose we have an explore like this:
In the customer view we have a measure like this:
In this situation it is probably safe to expose the maximum customer age as a measure in the order explore. To achieve this you would add a new measure to the order view, like this:
This method does not protect you from errors in and of itself. You must carefully consider if the interaction between the measure and your join will create inaccurate results.