[RETIRED] Error: 'filters:' is not supported for measures of non-aggregate type


(Zachary Michel) #1

This article has been retired as the information is now in documentation on this page .


(Brian Winkle) #2

What if I’d like to do something like this:

  type: number
  sql: ${total_food_revenue} - ${total_food_cost}
  filters:
    total_food_profit: 'not 0'```

Is that something I can only do by adding another measure that filters the first measure?

(lloyd tabb) #3

Brian,

Not sure what you are trying to compute or the result you are trying to achieve.

Are you trying to force a filter on the explore to never show 0 profits in aggregated results?

Are you trying to completely eliminate free orders from the data set? To do this I would add a net_cost dimension and then then always_filter (or and sql_always_filter) to remove the free orders.


#4

Zachary’s solution for dimensions only references the ‘WHERE’ clause with his sql parameter. What if we need to reference both the SELECT clause (to determine what is selected and how it is represented) as well as the WHERE clause (to filter the results)?

For example, how do I apply a filter the following dimension? If I add a filter condition to the end of this statement, it gets inserted into the SELECT clause.

- dimension: campaign
type: string
sql: |
        CASE WHEN ${TABLE}.campaign_id=100 THEN 'Welcome'
                   WHEN ${TABLE}.campaign_id=200 THEN 'Goodbye'
                   ELSE NULL
         END

(Sebastian Wells) #5

Bump. Any word on ross’ comment?


(Brecht Vermeire) #6

Hi @sebastian,

I’m not 100% sure what the expected result is here. Filtering on a dimension (ie. a not-aggregated column) would always mean you’re filtering in the WHERE clause, unless you’re referring to something else (e.g. a correlated subquery or a nested subquery).

For the first - depending on the database - Looker does support correlated subqueries (there’s a few good examples in this Looker Block). For any nested query structure, I’d say using a derived table would be the way to go.

It would be great if you had a specific example we can look at as well.