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 the existing Databricks connector.

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

Requirements​

  • 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.
  • A SQL warehouse.
  • If using Power BI Desktop, v2.151.1052.0 or above (February 2026 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.
  4. Under Data Connectivity mode, select DirectQuery.
  5. Complete the connection setup.

For detailed steps on manually creating a Power BI connection, see the connections guide for Power BI service or Power BI Desktop.

How BI compatibility mode works​

Metric views appear as regular tables in Power BI. When BI compatibility mode is enabled, Databricks rewrites the queries generated by Power BI to correctly query the metric view.

BI compatibility mode handles two types of queries automatically:

  • Aggregation queries: When dragging a measure into a value field in a Power BI visual, Power BI generates an aggregation query. BI compatibility mode rewrites these aggregations to adhere to the measure definitions in your metric view. Always use SUM as the aggregation type for measure columns in Power BI. The SQL engine always applies the correct underlying measure logic.
  • Data preview and schema discovery: When Power BI requests non-aggregated data (for example, View data or column previews), measure columns return null values instead of throwing an error. Dimension columns return their values normally.

Supported scenarios​

The following Power BI features work as expected when BI compatibility mode is enabled.

Scenario

Description

Basic measure visualization

Drag a measure into a chart or table value field to display aggregated results.

Filters

Apply filters to dimension or measure columns in your visuals.

Dimension slicers

Use dimension columns as slicer controls to filter your report.

Cross-filtering

Click a value in one visual to filter related visuals on the same page.

Drill-through

Right-click a data point to drill into a detail page filtered by that value.

TopN filtering

Show the top or bottom N values ranked by a column.

Data preview

Use View data and schema discovery. Measures display as null in previews.

Visual calculations

Client-side calculations applied to already-aggregated results (for example, running totals and ranking).

Dimensions vs. measures in Power BI​

Metric views contain two types of columns: measures and dimensions. Understanding the difference is important when building Power BI reports.

  • Measures: The aggregation logic of a measure is defined in the metric view (for example, SUM(price * quantity) or COUNT(DISTINCT customer_id)). In Power BI, always leave the aggregation set to SUM for measure columns. The SQL engine automatically applies the correct measure logic. If you need a different aggregation, modify the measure definition in the metric view itself. Don't change the aggregation on the Power BI side.
  • Dimensions: Dimensions behave like regular table columns. You can apply any Power BI operation to dimensions, including aggregations, grouping, filtering, sorting, and bucketing, just as you would with a regular table. If you have a numeric field defined as a dimension (not a measure), all standard Power BI aggregation types work normally on that field.

Best practices​

  • Always include a single metric view in your data set. The metric view is your semantic definition.
  • Create folders to organize dimension columns (for example, a "Date" folder for every dimension column from the Date dimension).
  • Rename dimensions to have user-friendly names.
  • Change numeric dimension columns to Don't Aggregate.
  • Create "wrapper measures" with SUM() for each measure column and hide the original measure columns (for example, Total Sales = SUM('Store Sales'[total_sales])).
  • Organize the measures into a "Measures" folder.
  • Use only these wrapper measures in your visuals.

Limitations​

Due to constraints of the Power BI connector SDK, BI compatibility mode has limited control over how Power BI generates and processes queries.

DirectQuery mode only​

BI compatibility mode requires DirectQuery connections. Import mode is not supported because it bypasses the Databricks query rewrite mechanism. When creating your Power BI connection, select DirectQuery as the data connectivity mode.

Only use SUM for measure aggregations​

In Power BI, always leave the aggregation type set to SUM for measure columns. All aggregation functions (SUM, COUNT, MIN, MAX) are rewritten to the underlying measure definition, so they all return the same result. Selecting a different aggregation type can lead to unexpected behavior:

  • AVG shows 1.0 because Power BI computes AVG as SUM / COUNT internally, and both return the same measure value.
  • Count (Distinct), Standard Deviation, Variance, and Median generate query patterns that are incompatible with the rewrite mechanism and produce errors or incorrect results.

If you need a different aggregation, modify the measure definition in the metric view. All aggregation types are fully supported within metric view definitions.

Grand totals for non-additive measures​

Power BI calculates grand totals by reaggregating per-group values on the client side rather than issuing a separate query. This produces correct results for additive measures (for example, SUM(revenue)) because reaggregating locally gives the correct answer.

However, for non-additive measures (for example, SUM(revenue) / COUNT(DISTINCT customer), or any ratio, percentage, or expression involving DISTINCT), grand totals might display incorrect values because summing pre-grouped ratios is not mathematically equivalent to computing the ratio over the full data set.

Quantitative slicers on measure columns​

A quantitative (range) slicer on a measure column does not work as expected. Power BI queries for the MIN and MAX of the measure to determine the slider range, but both are rewritten to the same underlying measure value, collapsing the range to a single point. Filters on measures still work. Only range slicers are affected.

Measures cannot be used as categorical or dimensional values​

If you drag a measure column to an axis, legend, or slicer as a categorical value, the query fails with the following 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.

Calculated fields with multiple measures​

Calculated fields that reference a single measure work correctly because Power BI fetches the aggregated result first and then performs the calculation on the client side (for example, bucketing revenue into low, medium, and high categories).

However, expressions that combine multiple measure columns inside a single aggregate (for example, SUM(m1 + m2)) are not rewritten by BI compatibility mode and produce errors or unexpected results.

Joining metric views with other tables​

Metric views cannot be joined with other tables in Power BI. If you need to combine data from a metric view with another table, incorporate the join into the metric view definition itself.

Materialized metric views​

Metric views with materializations are not supported in BI compatibility mode and might produce unexpected results.

Additional resources​