Direct Lake Fallback to DirectQuery — Causes, Diagnosis & Fixes

Two Direct Lake Modes — The Critical Distinction

Before diagnosing fallback, confirm which Direct Lake mode your semantic model uses. The two modes have fundamentally different failure behaviours, and confusing them leads to incorrect troubleshooting steps.

Benchmark testing (FourMoo, January 2026) recorded median query durations of approximately 174,000 microseconds for Import mode, 247,000 for Direct Lake, and 724,000 for DirectQuery. When Direct Lake falls back to DirectQuery, query latency typically moves from the 247,000 range into the 724,000 range — a roughly 3× degradation that becomes noticeable immediately for interactive reports.

How to Detect Direct Lake Fallback

The most common reason fallback goes undetected for weeks is that the default DirectLakeBehavior setting is Automatic — the engine falls back silently and reports continue to show data. The only visible symptom is slower visuals, which teams often attribute to DAX complexity or data volume rather than the storage mode change.

Method 1 — Performance Analyzer (Power BI Desktop)

Open Power BI Desktop with the report open. Navigate to View → Performance Analyzer → Start Recording. Interact with several representative visuals — particularly the slowest ones. After refresh, expand each visual in the Performance Analyzer pane. When Direct Lake is working correctly, visual load times for simple aggregations should be under 500ms and you will see only storage engine events. When fallback is active, simple aggregations take 1–5 seconds and you will see DirectQuery events in the log. That is the confirmation.

Method 2 — DAX Studio with Server Timings

Attach DAX Studio to the semantic model via the External Tools ribbon in Power BI Desktop. Enable Server Timings (F5). Run a simple aggregation DAX query — something like EVALUATE SUMMARIZE(SalesFact, SalesFact[Year], "Total", SUM(SalesFact[Amount])). When Direct Lake is active, the trace shows storage engine cache hits and no SQL text. When fallback is active, the trace shows SQL being generated against the warehouse or lakehouse endpoint — the SQL text appears in the server timings output. This is the most precise diagnostic method.

Method 3 — Fabric Capacity Metrics App

Open the Fabric Capacity Metrics app in your workspace. Filter by time windows when users report slowness. Look for spikes in DirectQuery workload that align with those sessions. DirectQuery CU consumption during what should be Direct Lake sessions confirms that queries are routing through the SQL endpoint rather than the in-memory path.

Method 4 — Refresh History Warning

When a Direct Lake on SQL model’s source Delta tables exceed the SKU guardrails, Power BI records a warning in the model Refresh History: “We noticed that the source Delta tables exceed the resource limits of the Premium or Fabric capacity requiring queries to fall back to DirectQuery mode.” Check the semantic model’s Refresh History in the Power BI Service for this warning — it is the definitive confirmation of a guardrail-triggered fallback.

The Four Root Causes of Fallback

Cause 1 — Guardrail Breach

Every Fabric F-SKU has Direct Lake guardrails — limits on the number of Parquet files per Delta table, the number of row groups, and the total number of rows. When a Delta table exceeds these limits for the assigned capacity, the refresh succeeds but the engine cannot frame the table into memory. All queries against that table fall back to DirectQuery.

The guardrail values are documented in the Direct Lake overview on Microsoft Learn and vary by F-SKU (F2 through F2048). They are updated as capacities evolve — always check the current live values rather than relying on cached numbers. If a table exceeds the limits for the largest F2048 SKU, refresh fails and the model always falls back to DirectQuery.

Cause 2 — SQL-Endpoint Security

When row-level security (RLS) or object-level security (OLS) is defined at the SQL analytics endpoint of the Lakehouse or Warehouse, the warehouse must participate in every secured query to enforce those rules correctly. The engine routes those queries through DirectQuery to guarantee that security policies are applied — staying on the Direct Lake in-memory path would bypass the SQL security layer.

This is the most common cause in enterprise architectures where security is centralised at the SQL layer. The fix is to move RLS to the semantic model level — define row filters in the Power BI model rather than in the SQL endpoint. This keeps fact tables eligible for in-memory execution while still enforcing security correctly.

Cause 3 — Non-Materialised SQL Views

Direct Lake works by reading physical Delta table files from OneLake. When the semantic model targets a SQL view — even a simple one — the SQL engine must evaluate that view’s logic (joins, computed columns, filters) on every query. VertiPaq cannot cache view logic as columnar segments because views have no underlying Delta files to frame.

The fix is to materialise the view logic into a Gold Delta table using a Spark notebook, Materialized Lake View, or Dataflow Gen2 — then point the semantic model at the physical table rather than the view. See the Fabric Lakehouse Tutorial for the full Bronze-Silver-Gold pipeline pattern that produces clean Gold Delta tables.

Cause 4 — Unsupported Constructs

Calculated columns and calculated tables that reference Direct Lake tables are not supported. The engine cannot materialise these without full table scans, which conflicts with the Direct Lake architecture. Move all calculated column logic upstream into the ETL pipeline — compute in Spark or Dataflow Gen2 and write the result as a column in the Delta table itself, then access it as a regular column in the semantic model. This eliminates the calculated column trigger and gives VertiPaq a standard column it can cache as a segment.

DirectLakeBehavior Property

The DirectLakeBehavior property controls what happens when a query condition triggers fallback. It has three values, each with a different trade-off between resilience and transparency.

How to Set DirectLakeBehavior

1. Via Power BI Service web modelling (recommended)Open the semantic model in the Fabric workspace → Select the model → Model Explorer → click on Semantic model at the top level → Properties pane → Direct Lake behavior dropdown → Select the desired value. This option has been available in the web modelling experience since February 21, 2024.
2. Via Tabular Editor v2.21.1+ using XMLA endpointConnect Tabular Editor to the semantic model via the XMLA endpoint → Select Model → Options → Direct Lake Behaviour → change from Automatic to DirectLakeOnly or DirectLakeOrEmpty. Note: after updating via Tabular Editor, the web modelling experience is no longer available for that model. This method also does not work for auto-generated default semantic models, which do not have an XMLA endpoint.

Fixes — How to Eliminate Fallback

Fix 1 — Compact Delta Tables (Guardrail Breach)

Run OPTIMIZE on tables that show the guardrail warning in Refresh History. OPTIMIZE compacts small Parquet files into larger ones, reduces row group count, and applies V-Order and Z-Order sorting to improve read performance for the specific filter patterns your reports use. After OPTIMIZE runs, refresh the semantic model and check whether the Refresh History warning disappears.

-- Run after each major data load or on a scheduled maintenance job
-- Replace table name, partition column, and Z-Order keys for your schema
OPTIMIZE lakehouse_gold.SalesFact
  PARTITIONED BY (YearMonth)
  VORDER
  ZORDER BY (CustomerKey, ProductKey);

-- Remove Delta versions older than 7 days after compaction
VACUUM lakehouse_gold.SalesFact RETAIN 168 HOURS;

Choose Z-Order columns that match the filter columns most commonly used in your reports — typically date keys, customer keys, and product keys. If the table still exceeds guardrails after OPTIMIZE, the only remaining options are to partition more aggressively to reduce the active partition size, or to upgrade to a larger F-SKU with higher guardrail limits.

Fix 2 — Move Security to the Semantic Model (SQL-Endpoint Security)

If RLS is currently defined at the SQL analytics endpoint, move the row filters into the Power BI semantic model as standard RLS roles. Define security filters in Manage roles in Power BI Desktop or Service, mapped to Entra ID security groups. Remove the corresponding SQL-layer RLS from the Warehouse or Lakehouse SQL endpoint once the semantic model RLS is validated. This keeps the largest fact tables eligible for Direct Lake in-memory execution while enforcing security correctly at the model level.

If SQL-endpoint RLS is mandatory for compliance reasons and cannot be moved, consider separating the secured tables into a dedicated semantic model so only the reports that need those security policies pay the DirectQuery cost — leaving other reports on clean Direct Lake models.

Fix 3 — Materialise Views into Gold Delta Tables (View-Triggered Fallback)

Replace SQL views in the semantic model with physical Gold Delta tables. The transformation logic that currently lives in the view should move upstream into the ETL pipeline — write it as a Spark notebook, Materialized Lake View, or Dataflow Gen2 transformation that runs before the semantic model is refreshed. The Gold Delta table becomes a simple columnar structure that VertiPaq can frame efficiently.

Fix 4 — Push Calculated Columns Upstream (Unsupported Constructs)

Remove calculated columns from Direct Lake tables in the semantic model. For each calculated column, write the equivalent computation in the upstream ETL — a Spark transformation, a Dataflow Gen2 step, or a Materialized Lake View definition — and store the result as a regular column in the Gold Delta table. Then reference that physical column in the semantic model rather than a calculated column expression. The semantic model still uses the same data, but VertiPaq now has a standard physical column to cache rather than a computed expression that triggers fallback.

Frequently Asked Questions