Power Query vs DAX: Where to Optimise for Maximum Performance

Slow Power BI reports almost always trace back to the same root cause: transformation and calculation work happening in the wrong place. Power Query and DAX serve different purposes and have different performance characteristics. Getting the split right between them can cut your processing time in half. Getting it wrong creates reports that frustrate users and erode trust in the analytics platform.

Understanding Performance Fundamentals

How Power Query processes data at the source

Power Query is your data pipeline engine, working directly at the source to transform and clean data before it reaches your model. Each step in the Query Editor gets executed sequentially at the data source whenever possible — filtering records from SQL Server pushes the filter operation down to SQL Server, leveraging the database's optimised query engine rather than bringing all data into memory first.

This query folding capability is the most important performance mechanism in Power Query. It dramatically improves performance by reducing data transfer and processing overhead. The M language evaluates transformations lazily — operations execute only when absolutely necessary, so you can stack dozens of transformation steps without immediate performance impact.

Certain operations break query folding, forcing Power Query to process data locally. Custom functions, complex merges, and some text operations trigger this, shifting processing from the optimised source system to your local machine. Understanding what breaks folding is more valuable than knowing what enables it.

When DAX calculations impact model performance

DAX executes calculations at query time rather than during data refresh. Every measure evaluates when users interact with reports, making formula efficiency critical for responsive dashboards.

Context transition is the most common performance killer. When you use CALCULATE or reference measures within row contexts, DAX must evaluate expressions for potentially millions of rows individually. Calculated columns consume storage space and evaluate only during refresh, while measures use minimal storage but recalculate with every user interaction. This trade-off is fundamental to understanding where to put business logic.

When DAX cannot push operations to the storage engine level, performance suffers as data gets processed row by row rather than in optimised batches. The goal is writing formulas that let the storage engine do as much work as possible.

Memory usage differences

Power Query uses memory temporarily during refresh — it spikes significantly higher than your final model size but releases once refresh completes. The VertiPaq engine compresses your final dataset, often achieving 10:1 compression rates, so a 1GB source dataset might occupy 100MB in your model.

DAX memory consumption is different. The formula engine caches intermediate results and maintains evaluation contexts during query execution. Complex calculations with many variables or iterative functions consume substantial RAM during user interactions. Memory pressure affects these engines differently — Power Query failures are clear error messages; DAX memory issues manifest as slow performance or timeouts, making diagnosis harder.

Optimise Data Loading with Power Query

Reduce data volume through early filtering and column removal

Apply filters as early as possible in your query steps, ideally right after connecting to your source. If you need the last 12 months, set date filters immediately rather than loading everything and filtering later. Remove unnecessary columns early — each column you keep requires memory and processing through every subsequent step. Power Query tracks and transforms all columns through each operation even if you will eventually remove them.

Converting column data types early also helps. Text converted to numbers or dates at the source optimises memory usage and enables better compression.

Leverage query folding

Check whether your steps are folding by right-clicking on query steps and looking for "View Native Query." If it appears, Power Query successfully translated your step into SQL. If it is greyed out, folding broke at that step and everything afterward processes locally.

Operations that fold: basic filters and column removal, simple joins between tables from the same source, standard aggregations, sorting and grouping. Operations that break folding: custom columns with complex logic, certain text functions, mixing data from different sources. Once folding breaks, it does not resume in later steps. Position folding-friendly operations before complex transformations.

Minimise complex transformations

Custom columns with intricate business logic rarely fold and process row by row locally. Consider pushing complex calculations to your data source through views or stored procedures, or handle them in DAX where the optimised engine can process them more efficiently.

Text manipulation functions — splitting, extracting, pattern matching — typically do not fold. Batch these together and apply them after all possible folding has occurred: complete filtering and joining first, then handle text transformations on the reduced dataset. Merging queries from different data sources always prevents folding and forces local processing.

Enhance DAX Performance Through Strategic Formula Design

Write context-aware measures

Design measures that leverage existing filter contexts and relationships rather than recalculating everything from scratch. A measure that uses ALL() to remove all filters and reapply them forces unnecessary recalculation. Work within the existing filter state and only modify the specific context you need to change.

Use variables to store intermediate calculations:

YoY Growth =
VAR CurrentSales = SUM(Sales[Amount])
VAR PreviousYearSales = CALCULATE(
    SUM(Sales[Amount]),
    SAMEPERIODLASTYEAR(Calendar[Date])
)
RETURN
DIVIDE(CurrentSales - PreviousYearSales, PreviousYearSales)

Variables serve dual purposes — they improve performance by preventing repeated calculations and make formulas more readable. Without variables, DAX recalculates identical expressions multiple times within the same measure. Test intermediate values independently to simplify debugging.

Optimise iterator functions

SUMX, FILTER, and RANKX become bottlenecks when processing large datasets row by row. Pre-filter data before applying iterators:

-- Slow: processes all rows
Slow = SUMX(Sales, Sales[Quantity] * Sales[Price])

-- Fast: pre-filter to relevant rows
Fast = SUMX(FILTER(Sales, Sales[Amount] > 100), Sales[Quantity] * Sales[Price])

Replace iterator functions with aggregation functions where possible. SUM(Sales[Amount]) performs much faster than SUMX(Sales, Sales[Amount]) because it uses optimised aggregation engines rather than row-by-row processing.

Replace slow functions with faster alternatives

Replace nested IF statements with SWITCH when evaluating multiple conditions against the same expression. SWITCH evaluates the expression once; nested IFs may re-evaluate it repeatedly.

Choosing the Right Tool for Each Operation

Handle data shaping in Power Query

Use Power Query for removing duplicates, standardising text formats, filling missing values, splitting or merging columns, handling data type conversions, and applying business transformations that do not depend on filter context. Handling these in Power Query reduces the data model size and eliminates the need for complex DAX calculations later.

Reserve complex business logic for DAX

DAX handles calculations that depend on context and relationships: year-over-year comparisons, running totals, percentage of parent, conditional calculations that change based on user selections, cross-table calculations, dynamic segmentation and grouping. These cannot be pre-calculated because they must respond to user interactions.

Move aggregations upstream when possible

Moving aggregations to Power Query or your data source reduces processing load and improves responsiveness. When you can pre-calculate totals, averages, or counts before data reaches the model, you shrink data volume and eliminate repetitive calculations. Database engines handle aggregations more efficiently than Power BI, especially for large datasets.

Pre-aggregated tables also enable incremental refresh strategies — refresh detailed data for recent periods while keeping summarised historical data static.

Advanced Performance Optimisation

Calculated columns versus measures

Use calculated columns for static calculations that rarely change and depend on row context — categorisations, flags, simple mathematical operations that are not aggregations. Use measures for aggregations, time intelligence, and calculations that must respond to filter context.

Design efficient data model relationships

Start with a star schema where fact tables connect to dimension tables through single-column relationships. Use integer keys for relationships — they process faster than text-based keys. Avoid bidirectional filtering unless absolutely necessary, since it creates ambiguous filter paths. Remove unnecessary relationships that create multiple filter paths between tables.

Monitor with built-in tools

The Performance Analyzer in Power BI Desktop shows exactly how long each visual takes to render and identifies bottlenecks. Run it on complex report pages and document baseline rendering times before optimising. DAX Studio provides deeper insight with detailed execution plans and server timings — connect it to your Power BI model to analyse individual measure performance. Query diagnostics in Power Query show which transformations take longest during data loading.

Implement incremental refresh for large datasets

Incremental refresh automatically manages historical data by keeping recent data in separate partitions while archiving older information. Configure it based on data's natural patterns — daily or monthly partition boundaries suit most business data. Power BI can skip unnecessary partitions during queries, dramatically improving performance for large fact tables.

The pattern that works consistently is this: use Power Query for everything you can do at the source or during load, and use DAX for everything that must respond dynamically to user context. When performance suffers, always check Power Query first — most slow reports start with bringing too much data into the model, not with inefficient DAX.