Using a measure to filter in Looker

[BrittanyWP]

I am not able to filter by a measure in Looker. 

For example, we have set up a measure of how many staff members a business has. I want to filter how many businesses have two or more staff members. 

This is how the measure is set up in LookerML:

measure: count_unique_staff {
group_label: "@{gl_counts}"
description: "Distinct staff user count"
type: count_distinct
filters: [pk_transaction_uuid: "-NULL"]
sql: ${user_uuid} ;;

No matter what number I put into the filter for unique staff members, the number of businesses does not change.

Please send help 🙂

[marcwo]

Hi @BrittanyWP 

when you are on the Explore page and apply that filter, does the SQL generated change? The expecation would be that the filter on the measure adds a HAVING clause to the SQL - is this happening for you?

Best
Marc

[BrittanyWP]

Hi @marcwo Thank you for the reply.

This is the SQL so it does have the HAVING clause: is that correct?

SELECT
mart_merchants_count
FROM
(SELECT
COUNT(DISTINCT mart_merchants.business_uuid ) AS mart_merchants_count,
COUNT(DISTINCT CASE WHEN ((( mart_transactions.transaction_uuid ) IS NOT NULL)) THEN mart_transactions.user_uuid ELSE NULL END) AS mart_transactions_count_unique_staff
FROM `yoco-data-warehouse-prod-8ef3.core_shared.mart_transactions` AS mart_transactions
LEFT JOIN `yoco-data-warehouse-prod-8ef3.core_shared.mart_merchants` AS mart_merchants ON mart_transactions.business_uuid = mart_merchants.business_uuid
WHERE (mart_transactions.is_tpv ) AND ((( mart_transactions.transaction_at_local ) >= ((DATETIME_ADD(DATETIME_TRUNC(DATETIME_TRUNC(CURRENT_DATETIME(), DAY), MONTH), INTERVAL -11 MONTH))) AND ( mart_transactions.transaction_at_local ) < ((DATETIME_ADD(DATETIME_ADD(DATETIME_TRUNC(DATETIME_TRUNC(CURRENT_DATETIME(), DAY), MONTH), INTERVAL -11 MONTH), INTERVAL 12 MONTH)))))
HAVING mart_transactions_count_unique_staff >= 2) AS t3
LIMIT 500

[marcwo]

The way I interpret that query, it tries to determine the count of distinct merchants who have at least two unique staff members conducting transactions (is_tpv) over the last 12 months.

Please check if all your joins are correct and if the primary keys are set correctly in LookML. For troubleshooting, I would also go ahead and query business_uuid as a dimension, and staff count as a measure and then apply the filter on the measure to ensure that everything works as expected.

[BrittanyWP]

Ok thank you so much. It must be something in the LookML. Let me check that out.