Metric Views + Window Functions not supported

[wrosa]

Hi @Hubert-Dudek I am getting following error: 

METRIC_VIEW_WINDOW_FUNCTION_NOT _SUPPORTED The metric view is not allowed to use window function (...) 

With the following definition:

- name: Sales net Total
- expr: SUM(MEASURE(`Sales net`)) OVER())

However following query works fine:

```

SELECT

category,

MEASURE(`Sales net`) as sales_net,

SUM(MEASURE(`Sales net`)) OVER()  AS sales_net_total

FROM sales_metric_view

GROUP BY category

ORDER BY sales_net DESC

```

[SteveOstrowski]

Hi @wrosa,

The error you are seeing (METRIC_VIEW_WINDOW_FUNCTION_NOT_SUPPORTED) is expected behavior. Metric views do not allow raw SQL window functions like SUM(...) OVER() directly in a measure's expr definition. This is by design because metric views use their own declarative window specification in the YAML definition rather than inline SQL window syntax.

WHAT IS HAPPENING

When you define a measure as:

- name: Sales net Total
expr: SUM(MEASURE(`Sales net`)) OVER()

The metric view engine rejects this because OVER() is standard SQL window function syntax, which is not permitted inside a measure expression. Measure expressions in metric views must be aggregate expressions (like SUM(), COUNT(), AVG(), etc.), optionally with FILTER clauses or arithmetic, but not SQL window functions.

HOW TO ACHIEVE THE SAME RESULT

You have two options depending on what you need.

OPTION 1: USE THE METRIC VIEW WINDOW MEASURE YAML SYNTAX

Metric views support "window measures" as a declarative feature. Instead of writing OVER() in the expr, you define a window block in the YAML. For example, if you want a grand total of Sales net across all rows, you would define it like this:

measures:
- name: Sales net
expr: SUM(sales_net_column)
- name: Sales net Total
expr: SUM(sales_net_column)
window:
- order: <some_dimension>
range: all
semiadditive: last

The "range: all" specification gives you the equivalent of OVER() (the entire dataset). The "order" field must reference one of your defined dimensions, and "semiadditive" controls how the value is summarized when that order dimension is not in the GROUP BY of your query.

Here is a more concrete example. If you have a date dimension:

dimensions:
- name: category
expr: category
- name: order_date
expr: order_date
measures:
- name: Sales net
expr: SUM(sales_net_column)
- name: Sales net Total
expr: SUM(sales_net_column)
window:
- order: order_date
range: all
semiadditive: last

Then you would query it:

SELECT
category,
MEASURE(`Sales net`) as sales_net,
MEASURE(`Sales net Total`) as sales_net_total
FROM sales_metric_view
GROUP BY category
ORDER BY sales_net DESC

This gives you the per-category sales net alongside the grand total, similar to what SUM(...) OVER() would produce.

Note: Window measures are currently an experimental feature. See the documentation for full details on available range options (current, cumulative, trailing, leading, all).

OPTION 2: USE THE WINDOW FUNCTION AT QUERY TIME INSTEAD

As you already discovered, you can use window functions in the query that reads from the metric view, just not inside the metric view definition itself:

SELECT
category,
MEASURE(`Sales net`) as sales_net,
SUM(MEASURE(`Sales net`)) OVER() AS sales_net_total
FROM sales_metric_view
GROUP BY category
ORDER BY sales_net DESC

This approach works because the OVER() is applied at query time after the metric view engine has resolved the MEASURE() calls. This is a perfectly valid pattern and may be simpler if you only need this calculation in one specific query.

DOCUMENTATION REFERENCES

Metric views overview:
https://docs.databricks.com/aws/en/metric-views/

Window measures in metric views:
https://docs.databricks.com/aws/en/metric-views/data-modeling/window-measures

Creating metric views:
https://docs.databricks.com/aws/en/metric-views/create/

In summary: the metric view definition language intentionally uses its own declarative window specification rather than SQL window function syntax. You can either define window measures in YAML using the window block, or apply standard SQL window functions at query time when reading from the metric view.

* 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.

[lpolanco85]

Hi Steve, I have a similar problem , I am trying to translate the following query into yaml

  SELECT

    -- ATTRIBUTES

    FechaReporte_Crm,

    zonaventa_CRM,

    ClaseOperacionComercialId_Crm,

    --BusinessPartner_Crm,

    -- MEASURES

    MEASURE(ContadorReporte_Crm) AS ContadorReporte_Crm,

    MEASURE(Efectividad_CRM) AS Efectividad_CRM,

    -- % DISTRIBUTION

    MEASURE(ContadorReporte_Crm) * 100.0

        / SUM(MEASURE(ContadorReporte_Crm)) OVER (

            PARTITION BY

            ---ATTRIBUTES IN SELECT LIST

            FechaReporte_Crm

                , zonaventa_CRM

               -- , ClaseOperacionComercialId_Crm

        ) AS Pct_Distribucion_Abs_Crm,

    ---ATTRIBUTES IN SELECT LIST

    MEASURE(Efectividad_CRM) * 100.0

        / SUM(MEASURE(Efectividad_CRM)) OVER (

            PARTITION BY FechaReporte_Crm

                , zonaventa_CRM

                --, ClaseOperacionComercialId_Crm

        ) AS Pct_Distribucion_Efectividad_Crm

FROM datalakekof_dev.comercial_vista.com_vwm_crm_efectividad_mx

WHERE 1=1

 AND zonaventa_CRM IS NOT NULL

  AND FechaReporte_Crm = '20260505'

GROUP BY ALL

ORDER BY 2;


Here is how my yaml looks like:

---FIX PROD CATALOG FROM PRD2 TO PRD

CREATE OR REPLACE VIEW datalakekof_dev.comercial_vista.com_vwm_crm_efectividad_mx

WITH METRICS

LANGUAGE YAML

AS

$$

version: 1.1

source:  datalakekof_dev.comercial_vista.com_vw_crm_efectividad_mx

dimensions:

  - name: ReporteId_Crm

    expr: ReporteId_Crm

  - name: Responsable_Crm

    expr: Responsable_Crm

  - name: BusinessPartner_Crm

    expr: BusinessPartner_Crm

  - name: ClienteTexto_Crm

    expr: ClienteTexto_Crm

  - name: FlagCancelacion_Crm

    expr: FlagCancelacion_Crm

  - name: MedioIngreso_Crm

    expr: MedioIngreso_Crm

  - name: Prioridad_Crm

    expr: Prioridad_Crm

  - name: Canal_Crm

    expr: Canal_Crm

  - name: Encuestador_Crm

    expr: Encuestador_Crm

  - name: CodigoProducto_Crm

    expr: CodigoProducto_Crm

  - name: FechaReporte_Crm

    expr: FechaReporte_Crm

  - name: RutaPreventa_Crm

    expr: RutaPreventa_Crm

  - name: NivelMercadeo_Crm

    expr: NivelMercadeo_Crm

  - name: UnidadOperativaId_Crm

    expr: UnidadOperativaId_Crm

  - name: UnidadOperativaId1_Crm

    expr: UnidadOperativaId1_Crm

  - name: AgenteOperadorId_Crm

    expr: AgenteOperadorId_Crm

  - name: AgenteOperadorTexto_Crm

    expr: AgenteOperadorTexto_Crm

  - name: NumeroParte_Crm

    expr: NumeroParte_Crm

  - name: EstadoReporteTexto_Crm

    expr: EstadoReporteTexto_Crm

  - name: Dia

    expr: Dia

  - name: Mes

    expr: Mes

  - name: Filtro2_Crm

    expr: Filtro2_Crm

  - name: Filtro1_Crm

    expr: Filtro1_Crm

  - name: PaisAbv_Crm

    expr: PaisAbv_Crm

  - name: GrupoVendedor_CRM

    expr: GrupoVendedor_CRM

  - name: GrupoOficina_CRM

    expr: GrupoOficina_CRM

  - name: DistribuidoraRepartoTexto_ECC

    expr: DistribuidoraRepartoTexto_ECC

  - name: RutaReparto_CRM

    expr: RutaReparto_CRM

  - name: ZonaVenta_CRM

    expr: ZonaVenta_CRM

  - name: ZonaVentaTexto_Crm

    expr: ZonaVentaTexto_Crm

  - name: ModoVenta_Crm

    expr: ModoVenta_Crm

  - name: AreaId_Crm

    expr: AreaId_Crm

  - name: MotivoId_Crm

    expr: MotivoId_Crm

  - name: MotivoTipoTexto_Crm

    expr: MotivoTipoTexto_Crm

  - name: MotivoCodigoId_Crm

    expr: MotivoCodigoId_Crm

  - name: MotivoTexto_Crm

    expr: MotivoTexto_Crm

  - name: CodigoGrupoTexto_Crm

    expr: CodigoGrupoTexto_Crm

  - name: MotivoGrupoId_Crm

    expr: MotivoGrupoId_Crm

  - name: ClaseOperacionComercialId_Crm

    expr: ClaseOperacionComercialId_Crm

  - name: ClaseOperacionComercialTexto_Crm

    expr: ClaseOperacionComercialTexto_Crm

  - name: ClaseOperacionComercialTextoLargo_Crm

    expr: ClaseOperacionComercialTextoLargo_Crm

measures:

  - name: ContadorReporte_Crm

    expr: SUM(ContadorReporte_Crm)

  - name: ContadorReporteResolucion_Crm

    expr: SUM(ContadorReporteResolucion_Crm) * 100

  - name: Efectividad_Crm

    expr: COALESCE(TRY_DIVIDE(ContadorReporteResolucion_Crm,ContadorReporte_Crm),0)    

$$

;      

The tricky part here is that I am not able to dinamycally transalate the dimensions that participate in the PARTITION BY clause so I need to do it at the metric view level.

Also notice level of detail in PARTITION BY contains minus 1 column than in SELECT columns list, so when adding an extra column to SELECT, we need to have the same columns in PARTITION BY minus the one lastly added.

Do you have any idea how to achieve this behavior?

Thanks in advance

[SteveOstrowski]

Hi @lpolanco85,

Good news: you can express this entirely inside the metric view. I reproduced your exact pattern (a percent-of-total whose PARTITION BY has one fewer column than the SELECT list) on a SQL warehouse and it works. The two pieces that unlock it are composable measures and a windowed expression that wraps MEASURE().

TWO RULES THAT MAKE THIS WORK

1. A measure's expr must be an aggregate, and to reference another measure you wrap it in MEASURE(). So your Efectividad measure should compose the two underlying measures rather than reference the raw columns directly:

measures:
  - name: ContadorReporte_Crm
    expr: SUM(ContadorReporte_Crm)

  - name: ContadorReporteResolucion_Crm
    expr: SUM(ContadorReporteResolucion_Crm) * 100

  - name: Efectividad_Crm
    expr: COALESCE(TRY_DIVIDE(MEASURE(ContadorReporteResolucion_Crm),
                              MEASURE(ContadorReporte_Crm)), 0)

2. For the percent-of-total columns, you can use SUM(MEASURE(x)) OVER (PARTITION BY ...) directly in the measure expr. The important detail I confirmed by testing: inside the OVER clause you reference the DIMENSION names, not the raw source columns. This is exactly what gives you the "PARTITION BY contains one fewer column than the SELECT" behavior you described, and you define it only once:

measures:
  - name: Pct_Distribucion_Abs_Crm
    expr: MEASURE(ContadorReporte_Crm) * 100.0
        / SUM(MEASURE(ContadorReporte_Crm)) OVER (PARTITION BY FechaReporte_Crm, ZonaVenta_CRM)

  - name: Pct_Distribucion_Efectividad_Crm
    expr: MEASURE(Efectividad_Crm) * 100.0
        / SUM(MEASURE(Efectividad_Crm)) OVER (PARTITION BY FechaReporte_Crm, ZonaVenta_CRM)

(FechaReporte_Crm and ZonaVenta_CRM here are the dimension names you defined in the dimensions block, not the underlying table columns.)

HOW IT BEHAVES WHEN YOU QUERY IT

With the measures defined once as above, the consumer query controls the level of detail and you never touch the metric view again:

SELECT
  FechaReporte_Crm,
  ZonaVenta_CRM,
  ClaseOperacionComercialId_Crm,
  MEASURE(ContadorReporte_Crm)        AS ContadorReporte_Crm,
  MEASURE(Efectividad_Crm)            AS Efectividad_CRM,
  MEASURE(Pct_Distribucion_Abs_Crm)   AS Pct_Distribucion_Abs_Crm,
  MEASURE(Pct_Distribucion_Efectividad_Crm) AS Pct_Distribucion_Efectividad_Crm
FROM datalakekof_dev.comercial_vista.com_vwm_crm_efectividad_mx
WHERE ZonaVenta_CRM IS NOT NULL
  AND FechaReporte_Crm = '20260505'
GROUP BY ALL
ORDER BY 2;

ClaseOperacionComercialId_Crm is in the SELECT, but the percent stays partitioned at the FechaReporte/ZonaVenta grain, so the distribution sums to 100 percent within each FechaReporte + ZonaVenta combination across all the Clase values. Add or remove a column in the SELECT and the partition stays fixed at the columns you named in the measure. In my test on sample data this produced exactly that result (percentages summing to 100 within each fixed partition).

If instead you want the denominator to follow whatever the query groups on (a true "percent of the visible total"), use an empty window and the denominator recomputes at the query grain automatically:

expr: MEASURE(ContadorReporte_Crm) * 100.0
    / SUM(MEASURE(ContadorReporte_Crm)) OVER ()

TWO THINGS TO KEEP IN MIND

- Reference dimension names (not raw source columns) inside PARTITION BY. Using the underlying table column there returns a grouping error, while the dimension name works.
- Every dimension you list in PARTITION BY needs to be present in the query's GROUP BY. If you partition by FechaReporte_Crm and ZonaVenta_CRM, those two must be in the SELECT / GROUP BY of the query. Filters in the WHERE clause are applied before the window, so the percentages are computed over the filtered, visible rows.

DOCS

Composability (defining measures in terms of other measures with MEASURE()):
https://docs.databricks.com/aws/en/metric-views/data-modeling/composability

Metric view YAML reference:
https://docs.databricks.com/aws/en/metric-views/yaml-ref

Querying metric views (MEASURE(), GROUP BY ALL):
https://docs.databricks.com/aws/en/business-semantics/metric-views/query

This keeps everything in one metric view, defined once, with the query free to add or drop dimensions without you having to maintain a separate measure per partition shape. Hope that helps, and feel free to share your final YAML if you would like another set of eyes on it.

* 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.