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.
