DirectQuery Not Supported: Fix With Composite Models

You connect live to the warehouse, build the report of your dreams, and publish — then visuals start failing with not-supported errors. Calculated tables refuse to create. A clever Power Query step works in Desktop and dies in the service. Time intelligence crawls. The promise of always-fresh data collides with a long list of things DirectQuery simply cannot do.

DirectQuery is a translation engine, not a data engine. Every visual becomes a native query against your source, which means every DAX function, every transformation step, and every modeling construct must survive translation. Anything without a native equivalent — stored computed tables, untranslatable steps, exotic DAX — fails at query time, often far from the authoring moment that introduced it. The errors feel random because the cause and the symptom live in different layers.

The escape routes are composite models and deliberate import fallback. Composite mode lets each table choose its storage: Import for dimensions, DirectQuery for the giant fresh facts, Dual for shared lookups. When translation cannot serve a pattern at all, full Import with scheduled refresh trades freshness for the complete feature set. This article maps the unsupported list, teaches per-table storage design, and shows the fallback path that ends the incident.

What DirectQuery Promises and What It Forbids

DirectQuery keeps no data — only metadata describing tables, columns, and relationships. When a visual renders, the engine generates a native query (usually SQL) against the live source, runs it, and displays the returned rows. The 1-GB model limit never applies because there is nothing to store, and every view reflects current source data without a refresh cycle. For giant fact tables and real-time dashboards, this is the only viable mode.

The price is the translation contract. DAX measures must convert into native query fragments the source understands; Power Query steps must fold into native operations; modeling constructs must have a query-time equivalent. Calculated tables have none — they materialize rows at refresh, and DirectQuery performs no import refresh to materialize into. Quick Insights vanishes for the same reason: it needs an in-memory scan the mode never builds. Automatic date and time hierarchies are unavailable, so drill-downs by year and month need an explicit date table.

Mode-proof design starts from these constraints rather than discovering them at publish. Favor simple SUM-based measures on DirectQuery facts, keep transformation steps foldable, and plan an explicit date table from day one. When a requirement needs an unsupported construct, that table becomes the composite-model Import candidate instead of a publish-day surprise. The mode works beautifully inside its contract and fails loudly outside it — both behaviors are features if you read the contract first.

-- Mode-proof measure: simple aggregation translates everywhere
Total Sales = SUM ( FactSales[SalesAmount] )
-- Prefer SUM-based measures on DirectQuery tables

The Usual Suspects: Tables, Columns, and Steps That Fail

Unsupported-operation errors cluster around three constructs. Calculated tables top the list: any DAX table expression meant to materialize rows has nowhere to land in a model that imports nothing. Calculated columns on DirectQuery tables face a narrower cell — expressions on relational sources are limited to single-row logic with no iterators like SUMX and no context modifiers like CALCULATE. Complex Power Query steps form the third cluster: an over-clever transformation with no native equivalent breaks the whole query, and multidimensional sources cannot use the editor at all.

Diagnosis is mechanical. The error or the failing visual names the table; open it in Power Query Editor and walk Applied Steps watching fold behavior, or inspect the DAX for table expressions and iterators sitting on DirectQuery tables. Reproduce against Import sample data if needed: constructs that work imported but fail live are translation casualties by definition. This comparison is the fastest classifier in the toolbox — same logic, two modes, different verdicts means the mode is the message.

Remediation follows the construct. Calculated tables become measures evaluated per visual, or Import tables inside a composite model. Unfoldable steps get simplified into native-friendly operations or pushed upstream into database views where the database optimizer owns them. Iterator-laden calculated columns become measures or move to Import tables. Each fix preserves the business logic while changing where it executes — the hallmark move of DirectQuery work is relocation, not reinvention.

-- Portable buckets: a measure replacing a calculated bucket table
Sales Band =
IF (
    SUM ( FactSales[SalesAmount] ) > 100000, "Large",
    IF ( SUM ( FactSales[SalesAmount] ) > 10000, "Medium", "Small" )
)

DAX That Translates and DAX That Does Not

DAX translation is the subtlest failure zone because partial success misleads. Simple aggregations — SUM, COUNT, AVERAGE over DirectQuery columns — translate into clean native SQL and fly. Time intelligence, complex iterators, and multi-table context juggling may translate into monstrous nested queries that time out, or refuse translation entirely. The visual shows numbers for small filters and dies on full-year views, which reads as flakiness while being perfectly deterministic query economics.

An explicit imported date table is the single highest-leverage fix. Mark it as the date table, keep it in Import storage even inside composite models, and aim all time functions at it. The engine then resolves date math locally against a tiny dimension and sends the source a simple date-range predicate instead of a calendar computation. YTD, parallel periods, and rolling averages that choked suddenly behave, because the hard part stopped traveling to the database.

Simplify remaining DAX toward translatable shapes. Replace iterator-over-relationship patterns with pre-aggregated Import tables where possible, break giant measures into VAR steps to isolate the offending fragment, and test each time function on production-scale data rather than samples. Performance Analyzer reveals the generated native query per visual — read it. When the SQL looks absurd, the DAX is asking absurdly; reshape the question until the SQL looks like something you would hand-write.

-- Translatable YTD: SUM over an imported date table's year filter
Sales YTD =
CALCULATE (
    SUM ( FactSales[SalesAmount] ),
    DATESYTD ( DimDate[Date] )
)

Composite Models: Per-Table Storage Done Right

Composite models end the all-or-nothing trap by assigning storage per table. Dimensions holding lookups become Import: slicers and group-bys resolve from memory with zero source chatter. Giant or real-time facts stay DirectQuery: visuals query current data without importing billions of rows. Shared dimensions become Dual, acting as Import when queried alone and joining DirectQuery facts efficiently from the same source. Calculated tables in composites are always Import, which quietly resolves the unsupported-table class of errors.

Cross-source relationships need respect. Tables from different sources relate as many-to-many with limited relationship behavior: DAX cannot fetch one-side values from the many side across the boundary, and groupings from Import tables get injected into native queries as materialized subqueries that can run slowly at scale. Keep cross-source joins narrow — low-cardinality keys, filtered grains — and push heavy integration upstream into one source or an Import staging table when the queries complain.

Design the mix deliberately and label it. A storage-mode standard — Import by default, DirectQuery only with a written freshness or volume reason, Dual for shared dimensions — stops mode sprawl. Review Model view's storage column each release like a dependency audit: every DirectQuery table should justify its per-query cost, and every Import table should justify its refresh weight. Models drift toward the mode of their first table; make that first choice conscious.

-- Composite-safe category total over Dual dimensions
Category Sales =
CALCULATE (
    SUM ( FactSales[SalesAmount] ),
    VALUES ( DimProduct[Category] )
)

Performance Rules for Live Queries

Performance in DirectQuery is source economics plus chatter control. Each visual interaction emits native queries, so the source's indexes on join keys, filter columns, and date ranges decide the latency floor. Missing indexes turn every visual into a full scan billed per click. Partner with the DBA early: share the slowest native queries from Performance Analyzer, index what they touch, and re-measure. DirectQuery performance is a joint product of model and database, never the model alone.

Chatter control is the modeler's half. Imported dimensions eliminate lookup queries entirely; Dual dimensions halve them; careful visual design halves them again by filtering early and avoiding high-cardinality groupings that explode native result sets. Table and matrix visuals cap at 125 columns past 500 rows from DirectQuery sources, so wide tables paginate painfully — prefer focused measures with MIN, MAX, FIRST, or LAST aggregations for wide layouts. Every slicer is a query emitter; design pages so the first paint needs few of them.

Cache and capacity complete the picture. The service caches DirectQuery results where valid, but cache reuse shrinks when reports convert live connections into local models — workload and refresh-failure risk both rise. Monitor query volumes per report, set expectations with stakeholders that live data costs seconds where imported data costs milliseconds, and keep an Import fallback branch of critical pages for outage days. Freshness is a feature with a latency price; quote the price honestly.

The Import Fallback: Retreating Without Losing

Full Import fallback is the honorable retreat when translation cannot serve the pattern: unsupported source features, untranslatable core logic, or query economics no index can fix. Switching DirectQuery to Import is allowed (the reverse is not), so the path exists by design: import the data, regain calculated tables, full DAX, Quick Insights, and automatic date hierarchies, and pay with scheduled-refresh latency instead of per-query latency. For many operational reports, yesterday-fresh data at millisecond speed beats live data at nine seconds per click.

Migrate without forking logic. Keep measure definitions identical across the switch — SUM-based totals behave the same in both modes — so validation reduces to comparing totals between the old live report and the new imported one. Schedule refresh after source ETL completes, add the currency measures from refresh operations practice, and confirm Refresh history greens across several cycles before decommissioning the live version. Run both side by side during the transition week with a clear banner marking which is canonical.

Choose the mode per report, not per career. Real-time monitoring keeps DirectQuery with composite Import dimensions; monthly board packs go full Import; exploratory analysis prototypes composite and commits to whichever side the queries favor. Document the choice and its reason beside the model. The teams that suffer are not the ones on Import or DirectQuery but the ones that drifted into a mode without deciding — decide, write it down, and revisit yearly.

-- Fallback-safe total: identical logic in Import and DirectQuery
Total Sales (fallback safe) = SUM ( FactSales[SalesAmount] )
-- Keep one measure definition so mode switches never fork logic