Filter a dimension by a measure

[rita_d-16336967]

Hi everyone. 

Is it possible to filter a dimension by a measure, like we would do using “having” in SQL?

I know it’s probably possible by creating a derived table, but I’m wondering if there is a simpler way.

Thanks in advance.

[Dawid]

Can you add some more specifics? You can still filter by measure by clicking the filter icon - that in turn creates HAVING clause

[minerkt]

Hi @rita_d-16336967 

I think there are a couple ways to accomplish this depending on your use case.

The simplest option would be at the report level, using a measure as a filter.  There you have a lot of options as to how you filter.

Would this work for you?  If not, what are you attempting to accomplish?

Let me know if that helps!

[rita_d-16336967]

Hi @minerkt and  @Dawid . Thank you for looking into this.

So the report-level option is not relevant for me, because I’m trying to create a dimension by which I’d like to filter the whole dashboard - it is a “yesno” type, and if a user selects “yes” this should mean that the condition of count_of_something>=10 OR another_condition is true.

Actually it would look something like this:

  dimension: mydimension {
    type: yesno
    sql: regexp_extract(lower(${TABLE}.name),"test") is null or ${TABLE}.count_of_something>=10 ;;
  }

Where name is another dimension, and count_of_something is a measure. However, the code above won’t work, it causes the following error:

Field references an aggregate "table.count_of_something" but is specified as a "dimension". If you want to use aggregations such as sum, average, count, use a measure type instead.

[minerkt]

@rita_d-16336967 

I have a few thoughts looking at your response.

At the dashboard level, you can add filters to the dashboard and each report can be set with “listeners” that will check against the dashboard filters.  This way, your filter dimension could be created and applied to as many reports on your dashboard as you’d like.

Additionally, that ”mydimension” above, I would break into two separate dimensions and lose the OR statement.  Once you have the two specific dimension filters working separately, I would create a third option that combines the two dimensions rather than referencing the SQL tables.

If you break out that filter dimension such that the “count_of_something” is its own measure, you might have more luck avoiding that error.