[Feature] Allow metrics/measures to be referenced in filters and where clause

[Jstein77]

Is this your first time submitting a feature request?

• I have read the expectations for open source contributors
• I have searched the existing issues, and I could not find an existing issue for this feature
• I am requesting a straightforward extension of existing metricflow functionality, rather than a Big Idea better suited to a discussion

Describe the feature

Currently, filters and the where clause can only reference dimensions. This is overly restrictive, and there are valid cases where I might want to filter by a metric value. For example, I might want to calculate total revenue but only include users who made less than 3 orders in the last 10 days. The SQL to calculate this metric is simple:

select 
 sum(revenue) as revenue
from orders
where order_count_l10d < 3

However, this is challenging to do in MetricFlow and requires you to precompute the order_count_l10d on your dbt model, before referencing it as a dimension.

Describe alternatives you've considered

The only available workaround is to duplicate the aggregation logic in your dbt model and make a separate semantic model as a dimension source. Very much not ideal.

Who will this benefit?

No response

Are you interested in contributing this feature?

No response

Anything else?

No response

[kmclaugh]

Really, you ought to be able to filter by measures. For instance, "What is the total revenue for orders with a shipping amount less than $10?"

You should be able to run this command:

mf query --metrics revenue_total --group-by metric_time --where "{{Measure('order_id__shipping_amount')}} > 10"

You can work around it by recreate every Measure as a dimension, but that's a little annoying

[giokincade]

I'd love to see something like this.

@kmclaugh In order to recreate a measure as a dimension, wouldn't you have to create a separate model that aggregates the source model? Which then requires you to make choices about what granularity or group-by keys to aggregate by? Maybe I'm misunderstanding but that feels really arduous and probably only feasible for a handful of keys/dimensions you may want to aggregate by.