Returning a measure filtered on a measure from a joined explore could return unexpected results. For example if the primary explore is “Users” and a “Sales” explore is left joined to it and we try to return a count of users who have ten or more sales, Looker would generate the following SQL:SELECT
COUNT(*) AS users.count
FROM
users
LEFT JOIN sales ON users.id = sales.user_id
HAVING (( COUNT( DISTINCT sales.id ) ) >= 10)
Without a group by, the having clause will have no effect on the count of users returned. Whereas if we selected users.id instead of a count, we’ll get the expected results. Is it possible Looker could generate this query differently? For example could Looker put
SELECT
users.id
FROM
users
LEFT JOIN sales ON users.id = sales.user_id
GROUP BY 1
HAVING (( COUNT( DISTINCT sales.id ) ) >= 10)
into a CTE and then select a count of users.id from that? Forgive me if I missed an explanation for this behavior in doc somewhere.