I have a Custom measure that filters orders by a service, for simplicity it looks something like this:
Measure name: special_service
Field to measure: orders
Filter: ${service.name} = "some service"
then I want to further filter this one by a specific product, let’s say like this:
Measure name: special_product
Field to measure: special_service
Filter: ${product.name} = "something"
The problem is that my second measure, in SQL, takes orders in its CASE missing the first one…
COUNT(DISTINCT CASE WHEN product.name = 'something' THEN orders.id ELSE NULL END)
but I expected this:
COUNT(DISTINCT CASE WHEN service.name = 'some service' AND product.name = 'something' THEN orders.id ELSE NULL END)
or alternatively:
COUNT(DISTINCT CASE WHEN service.name = 'some service' THEN CASE WHEN product.name = 'something' THEN orders.id ELSE NULL END ELSE NULL END)
Even though I select special_service as my base for calculating custom measure, it’s completely missing the service context…