Skip to main content
Last updated on

Query metric views in Power BI

Beta

This feature is in Beta.

BI compatibility mode lets you query Unity Catalog metric views in Power BI using standard SQL aggregation functions, without requiring changes to your Power BI reports or data models.

This page covers requirements, how to enable BI compatibility mode, how it works, supported query patterns, and known limitations.

Requirements​

  • A SQL warehouse running Databricks Runtime 18.0 or above
  • Power BI connections using the ADBC driver, which is enabled by default for new connections. For more information, see Configure ADBC or ODBC driver for Power BI.
  • If using Power BI Desktop, v:2.151.1052.0 or above (February 2025 release) is required.

Enable BI compatibility mode​

Enable BI compatibility mode when manually creating a new Databricks connection in Power BI Desktop or service:

  1. In Power BI Desktop or service, create a new connection to Databricks.
  2. Enter your Databricks Server Hostname and SQL warehouse HTTP Path.
  3. Under Advanced Options, set Metric View BI Compatibility Mode to Enabled under Advanced configurations.
  4. Complete the connection setup. For detailed steps on manually creating a Power BI connection, see how to connect to Power BI service or Power BI Desktop.

How BI compatibility mode works​

BI tools often fail to differentiate between measures and dimensions, treating every column as a standard data field. This leads to several issues:

  • Aggregation mismatch: To query a measure, users use the MEASURE() function (for example, SELECT region, MEASURE(totalrevenue) FROM mv GROUP BY region), but BI tools generate standard SQL like SELECT region, SUM(totalrevenue) FROM mv GROUP BY region.
  • Disaggregated queries: BI tools use SELECT * or non-aggregated queries to populate data previews, fetch column domains, or perform "view data" operations. Without compatibility mode, these queries fail because measures require an aggregation context.

BI compatibility mode handles all of these cases automatically inside Databricks Runtime. When enabled, it applies the following transformations to queries on metric views.

Aggregate rewriting​

Standard aggregate functions applied to measure columns are automatically rewritten to MEASURE():

BI tool sends

Databricks rewrites to

SUM(measure_col)

MEASURE(measure_col)

COUNT(measure_col)

MEASURE(measure_col)

MAX(measure_col)

MEASURE(measure_col)

MIN(measure_col)

MEASURE(measure_col)

This means the metric view's original measure definition is always respected, regardless of which aggregate function the BI tool wraps around it. For example, if a measure is defined as SUM(price * quantity), then COUNT(total_revenue), AVG(total_revenue), and SUM(total_revenue) all return the same result, which is the underlying SUM(price * quantity).

Disaggregated queries​

When measure columns are selected without an aggregate function (for example, SELECT * or SELECT measure1, measure2 FROM model), null values are returned instead of throwing an error.

Dimension columns return their values normally. Only measure columns are affected.

Supported query patterns​

The following query patterns run as intended when BI compatibility mode is enabled.

Scenario

Example

Basic aggregation queries

SELECT dim, SUM(measure) FROM mv GROUP BY dim, with SUM rewritten to MEASURE().

ORDER BY with measures

SELECT region, SUM(total_cost) FROM mv GROUP BY region ORDER BY SUM(total_cost) DESC

HAVING clauses with measures

SELECT region, SUM(total_cost) FROM mv GROUP BY region HAVING SUM(total_cost) > 20

Window functions over measures

SUM(SUM(total_cost)) OVER (PARTITION BY region ORDER BY sku)

Existing MEASURE() calls

If a query already uses MEASURE(), it runs without double-wrapping.

Non-measure aggregates

Standard aggregates on non-measure columns, such as COUNT(DISTINCT sku), work as expected and are not rewritten.

SELECT * and disaggregated queries

Measure columns return null values, which supports data preview and schema discovery.

Best practices​

  • Leave the default aggregation as SUM for all measure columns. Changing to other aggregations, such as COUNT, DISTINCT, VARIANCE, might cause errors.
  • Do not use measures as categorical values. Instead, drag measure columns into value or quantitative fields in your visuals.

Limitations​

The following limitations apply when using BI compatibility mode with Power BI.

Identical aggregation results​

Because SUM, COUNT, MIN, and MAX are all rewritten to MEASURE(), they return identical values. This has two side effects:

  • Grand totals in pivot and matrix visuals might display unexpected values. For example, selecting COUNT as the aggregation shows a sum in the grand total row.
  • The AVG aggregation shows 1.0. Power BI computes AVG as SUM / COUNT internally. Because both return the same measure value, the result is approximately 1.

To avoid unexpected totals and averages, use SUM as the default aggregation for all measure columns in Power BI.

Unsupported aggregations throw errors​

The following aggregation types send queries without an aggregate wrapper, which metric views don't support for measure columns:

  • Count (Distinct)
  • Standard Deviation
  • Variance
  • Median

Measures can't be used as categorical or dimensional values​

If you drag a measure column to an axis, legend, GROUP BY, or slicer as a categorical value, the query fails with this error:

Text
Measure columns cannot be used in GROUP BY clause or as categorical values. We recommend wrapping them with an aggregate function such as SUM() for the expected behavior.

Don't use measure columns as dimensions, group-by fields, or filter targets.

Calculated fields with multiple measures​

Expressions like SUM(m1 + m2) combine multiple measure columns in a single aggregate and aren't rewritten by BI compatibility mode. These expressions produce errors or unexpected results.

Power BI quantitative slicers on measure columns silently fail​

A quantitative slicer on a measure column appears to work but doesn't apply the filter because metric views don't support using measures as filter predicates.

Dimension filtering and slicing​

When using BI compatibility mode, Power BI visuals do not support the following operations on dimension columns:

  • Applying filters to dimensions
  • Using dimensions as slicer controls
  • Cross-filtering dimension values between visuals

Additional resources​