topic Re: Genie generates MEASURE expression with "filter" clause in Data Engineering

Genie generates MEASURE expression with "filter" clause

Malthe — Fri, 20 Feb 2026 10:05:24 GMT

Genie generated a query against a metric view that introduces a "filter" clause as a second parameter to MEASURE:

SELECT `countryName`, MEASURE(`deviceCount`, `isActive` = true) AS `online`, MEASURE(`deviceCount`, `isActive` = false) AS `offline` FROM `dev`.`test`.`my_metric_view` GROUP BY ALL WITH ROLLUP

Is that a hallucination or an indication that filtering measures will become a feature in a future release?

If it's the former, how are we supposed to defend against such a hallucination?

Re: Genie generates MEASURE expression with "filter" clause

Malthe — Sun, 22 Feb 2026 08:04:58 GMT

Note that sometimes, Genie also generates FILTER expressions:

SELECT MEASURE(`deviceCount`) FILTER(WHERE `isActive` = TRUE)

It seems that the underlying model is unable to accurately grasp the grammar around the MEASURE function.

Re: Genie generates MEASURE expression with "filter" clause

Commitchell — Tue, 24 Feb 2026 13:47:27 GMT

Hi @Malthe.

This does appear to be a hallucination. I would recommend you submit a support ticket if possible, so this gets correctly documented for the product team.

As for how to defend against it, my recommendation is to invest in Metadata and Configuration. Metadata as a part of the metrics can define things like "deviceCount is defined with the filter "Where isACTIVE = TRUE."

From a configuration/instructions perspective, you can add instructions and sample queries to the Genie space to mitigate this. Using the combination of sample queries, instructions, and benchmarks (see Knowledge Store) you can curate a Genie space's behavior to account for this until we improve the capability.

I see tons of investment in this space and Metric Views are still relatively new. I have faith this will be fixed soon.

Re: Genie generates MEASURE expression with "filter" clause

Malthe — Tue, 24 Feb 2026 14:00:14 GMT

I'm unable to file a support issue currently, but I wouldn't expect that I had to instruct the Genie to craft correct queries, syntactically ...

Re: Genie generates MEASURE expression with "filter" clause

Commitchell — Tue, 24 Feb 2026 14:28:41 GMT

I agree that'd be nice, and I believe we'll get there. These tools are improving daily.

However, despite the cute name "Genie," this technology is not magic. LLMs are non-deterministic. I'm obviously bias, but Databricks provides the most robust capabilities to curate these models to be more consistent, reliable, cost-effective, and trustworthy. Sometimes that means you have to provide metadata, examples, or instructions to augment the model's training.

As you're unable to file a support ticket, I'll make sure to share your experience with the product team directly. We appreciate the feedback!

Re: Genie generates MEASURE expression with "filter" clause

Malthe — Wed, 25 Feb 2026 08:30:32 GMT

Thanks. I understand it's not magic, but a very trivial improvement would be to validate the generated query before presenting it to the user. If it doesn't validate, then on the Genie side something has to happen and certainly a syntax error should be presented with a caption such as a "Genie is currently unable to form a valid query".

Re: Genie generates MEASURE expression with "filter" clause

Commitchell — Wed, 25 Feb 2026 12:17:11 GMT

I agree. If you've seen Genie "Agent Mode," that's exactly how it works. We strive to provide this type of transparency throughout the product experience. Keep the feedback coming!

Hi @Malthe, The behavior you are seeing is indeed the LLM...

SteveOstrowski — Mon, 09 Mar 2026 05:46:01 GMT

Hi @Malthe,

The behavior you are seeing is indeed the LLM generating invalid SQL syntax. The MEASURE() function takes exactly one argument, which is a reference to a measure column defined in a metric view. There is no second "filter" parameter, and MEASURE does not support the FILTER(WHERE ...) clause that standard aggregate functions like SUM or COUNT support.

So both of these generated forms are syntactically invalid:

MEASURE(`deviceCount`, `isActive` = true)

MEASURE(`deviceCount`) FILTER(WHERE `isActive` = TRUE)

CORRECT SYNTAX

The MEASURE function signature is simply:

measure( measure_column )

It inherits its aggregation logic entirely from the metric view definition. Documentation reference:
https://learn.microsoft.com/en-us/azure/databricks/sql/language-manual/functions/measure

HOW TO ACHIEVE FILTERED MEASURES

The correct approach is to define the filtered aggregation inside the metric view itself. In the YAML definition of a metric view, individual measures can include inline filter expressions. For example:

CREATE OR REPLACE VIEW my_metric_view
(countryName, deviceCount, onlineDeviceCount, offlineDeviceCount)
WITH METRICS
LANGUAGE YAML
AS $$
version: 0.1
source: your_catalog.your_schema.your_table
dimensions:
- name: countryName
  expr: countryName
measures:
- name: deviceCount
  expr: COUNT(deviceId)
- name: onlineDeviceCount
  expr: COUNT(deviceId) filter (where isActive = true)
- name: offlineDeviceCount
  expr: COUNT(deviceId) filter (where isActive = false)
$$;

Then your query would use separate MEASURE calls:

SELECT
`countryName`,
MEASURE(`onlineDeviceCount`) AS `online`,
MEASURE(`offlineDeviceCount`) AS `offline`
FROM `dev`.`test`.`my_metric_view`
GROUP BY ALL
WITH ROLLUP

This is the supported pattern: push the filter logic into the metric view definition as separate named measures, then reference each one with a simple MEASURE() call.

MITIGATING GENIE SQL GENERATION ISSUES

To reduce the likelihood of Genie generating invalid syntax against your metric views, you can use several features in the Genie knowledge store:

1. Sample queries: Add example SQL queries to your Genie space that demonstrate the correct MEASURE() syntax. Genie uses these as reference when generating new queries.

2. Instructions: Add explicit instructions such as "The MEASURE function accepts only one argument. Never pass a filter condition as a second argument to MEASURE. Never use FILTER(WHERE ...) with MEASURE."

3. SQL expressions: Define measures, filters, and dimensions in the knowledge store under Configure > Instructions > SQL Expressions. This gives Genie structured definitions for your business metrics.

4. Separate measures in the metric view: As shown above, define distinct measures for each filtered variant. This makes it straightforward for Genie to reference the correct measure by name.

Documentation for the knowledge store:
https://learn.microsoft.com/en-us/azure/databricks/genie/knowledge-store

You may also want to look at Genie Agent Mode (currently in Beta), which uses multi-step reasoning and can iteratively refine its queries:
https://learn.microsoft.com/en-us/azure/databricks/genie/agent-mode

Your point about validating generated SQL before presenting it to users is well taken. The product team continues to invest in improving Genie's accuracy with metric views, and feedback like this is valuable for prioritizing those improvements.

* This reply used an agent system I built to research and draft this response based on the wide set of documentation I have available and previous memory. I personally review the draft for any obvious issues and for monitoring system reliability and update it when I detect any drift, but there is still a small chance that something is inaccurate, especially if you are experimenting with brand new features.

If this answer resolves your question, could you mark it as "Accept as Solution"? That helps other users quickly find the correct fix.