Slow reports lose your audience. If your Power BI dashboard takes ten seconds to refresh every time someone clicks a filter, your stakeholders will stop using it, no matter how clever your analysis is. That is why Advanced DAX and Query Optimisation matters: it is the skill that turns a clunky, sluggish report into a fast, responsive tool that people actually want to use during meetings.
Jump to a section
A simple way to picture Advanced DAX and Query Optimization
70 minutes. That is exactly how long it takes to roast a large chicken for a Sunday dinner, and learning to optimise DAX is like learning to cook that meal efficiently for a family of eight. You have the fridge (your data model), the cooker (Power BI’s VertiPaq engine), and a set of recipes (your DAX measures). Imagine you want to make gravy while the chicken rests. You grab the roasting pan, pour the juices into a saucepan, and whisk in flour. This is your measure — a simple formula that uses data already nearby. But if you decide to roast the chicken first, then make the gravy from scratch by boiling a separate stock, you create unnecessary steps. In DAX terms, this is a slow measure that forces the engine to scan many rows or recalculate the same value repeatedly. Now picture three different guests asking for their own special gravy: one gluten-free, one vegan, one traditional. If you make three separate gravies from scratch, you waste time and ingredients. The smarter approach is to make one excellent base gravy, then adapt it quickly for each guest. That is exactly how query optimisation works: write one efficient base measure that does the heavy lifting once, then reuse it in other calculations. The Sunday roast teaches you that the best cooks plan for speed and reuse, the same way the best DAX developers write measures that minimise the work Power BI’s engine has to do.
To understand advanced DAX and query optimisation, you first have to understand what happens when you drop a measure into a visual. When a Power BI report loads or when a user changes a filter, Power BI sends a query to the engine underneath, which is called VertiPaq. VertiPaq is an in-memory column-store database. Think of it as an incredibly fast spreadsheet that stores every column of your data compressed in memory, much like having the entire table in RAM on your computer. Every time you write a DAX measure, VertiPaq has to find and calculate numbers from this compressed store. The speed of that calculation depends on three things: how much data it has to look at, how complex the formula is, and how efficiently the storage engine can access the data.
Let us start with storage engine versus formula engine. These are the two parts of Power BI’s query processing. The storage engine (VertiPaq) does the heavy lifting of scanning compressed columns. It is optimised for scanning large amounts of data quickly, but it cannot handle complex logic like nested IFs or OR conditions. The formula engine is the part that handles complex calculations, row-by-row logic, and functions like SUMX, FILTER, or CALCULATE. When your DAX formula is simple, the storage engine can handle most of it. When your formula gets complicated, the formula engine takes over, which is much slower. Optimising DAX means writing measures that push as much work as possible to the storage engine and give the formula engine as little to do as possible.
Now, there are several specific techniques to achieve this. The first is minimising the number of rows scanned. A measure that reads an entire table of ten million rows is slower than one that reads only the rows needed for the current filter context. You can achieve this by using CALCULATE to modify filter context, by creating well-designed relationships, and by using variables to store intermediate results so the engine does not have to recalculate the same expression multiple times.
The second technique is avoiding expensive functions. Certain DAX functions are inherently slower because they force the formula engine to iterate row by row. Functions like SUMX, COUNTX, and other X-iterators are powerful but should be used sparingly. A common optimisation is to replace an X-iterator with a simpler measure that uses CALCULATE and SUM, which can often be handled entirely by the storage engine. For example, instead of writing SUMX(Sales, Sales[Quantity] * Sales[Price]), you could add a calculated column for the total (which is computed once at refresh) and then just SUM that column. The calculated column stores the result, so the measure is just reading precomputed values.
The third technique is managing the data model itself. This is often called star schema design. In a star schema, you have one fact table (the central table containing transactional data, like sales) surrounded by dimension tables (like Customer, Product, Date). This design reduces the amount of data VertiPaq has to scan because filtering happens on the small dimension tables, and the engine only scans the fact table for the required rows. If you have a single flat table with millions of rows and no relationships, every filter forces the storage engine to scan the whole table. That is why normalising your model into a star schema is the number one performance tip.
Fourth, you should learn to use variables. Variables in DAX, declared with VAR, let you store an intermediate calculation and then reuse it later in the same measure. This prevents the formula engine from calculating the same expression twice. For example:
VAR TotalSales = SUM(Sales[Amount]) VAR TotalCost = SUM(Sales[Cost]) RETURN TotalSales - TotalCost
The engine calculates TotalSales once and TotalCost once, then subtracts them. Without variables, you might write TotalSales - TotalCost directly, but if the same TotalSales is used in another part of the measure, it gets recalculated. Variables also make your code easier to read and debug.
Finally, understand filter context and row context. Filter context is the set of filters applied to a calculation — for example, the current year and product selected in a slicer. Row context applies to calculated columns and iterator functions, where each row is evaluated individually. When you write a measure inside a visual, it automatically uses filter context. When you write a calculated column, it uses row context. Mixing them incorrectly (for example, using a column reference that relies on row context inside a measure) can produce wrong results or slow down performance because the engine has to switch contexts.
Query optimisation also includes managing the number of visuals on a page and the complexity of each visual. Every visual sends at least one query to the engine. Ten complex visuals on one page can cause the engine to process ten separate queries, which slows down the report. You can reduce this by using fewer visuals, by using measure-level calculations instead of row-level ones, and by using the 'Performance Analyser' pane in Power BI Desktop to identify which visuals take the longest to load.
Identify the Slow Visual
Open your Power BI report and go to the View tab. Click on Performance Analyser. Click 'Start recording' and then interact with your report (e.g., change a slicer, click a visual). The pane records the duration of each visual's query. Look for the visual with the highest 'DAX Query' duration. That is your target. Write down its name and duration.
Examine the Underlying Measure
Right-click the slow visual and select 'Visual' then 'Expand to see measure details'. Note which measure(s) it depends on. Open the 'Data' pane, find that measure, and review its DAX code. Look for any iterator functions like SUMX, COUNTX, AVERAGEX, or FILTER. Also look for nested CALCULATE calls or repeated expressions. If you see the same expression used twice without a variable, that is a red flag.
Rewrite the Measure Using Variables and Simple Aggregations
Introduce VAR to store intermediate results. For example, if the measure had 'SUMX(Sales, Sales[Quantity] * Sales[Price])', change it to add a calculated column for the line total first. Then the measure becomes 'SUM(Sales[LineTotal])'. If the measure calculates a ratio, break it into two base measures: Total Sales and Total Cost, then create a third measure that divides them. This is measure branching.
Optimise the Data Model
Check if your data model is a star schema. Go to the Model view. If you have a single table with many columns, it is a flat table. Split it into a fact table (the transactional table with numbers and foreign keys) and dimension tables for each descriptive entity (e.g., Product, Customer, Date). Create relationships between them. Ensure that dimension tables have a primary key column that links to the fact table. Delete any redundant columns that are not needed for analysis.
Test and Measure Again
Go back to the Performance Analyser. Click 'Clear' to remove previous recordings. Start a new recording and perform the same interaction that previously caused the slow visual. Compare the new duration with the old one. If the visual is still slow, check for high-cardinality columns (columns with many unique values) in dimensions, or consider reducing the number of visuals on the page. Repeat the process until the visual loads under 2 seconds.
Sarah is a data analyst at a mid-size retail company. She built a Power BI report that tracks daily sales across 50 stores. The report contains a slicer for the date range, a slicer for the region, and a slicer for the product category. Her manager uses the report every morning to see which stores need restocking. Initially, Sarah’s report worked fine with a month of data. But as the company added more products and stores, the dataset grew to cover two years of transactions — about 5 million rows. Then the report started taking 30 seconds to respond to every slicer change. Her manager complained that the report was unusable.
Sarah opened the Performance Analyser pane in Power BI Desktop and recorded a session. She saw that one visual, a matrix showing total sales by store and product, was taking 12 seconds to render. She examined the measure for that visual. The measure was:
Total Sales = SUMX(Sales, Sales[Quantity] * Sales[Unit Price])
This measure forced the formula engine to iterate over every row of the Sales table every time the visual refreshed. Sarah realised she could optimise this. She added a calculated column to the Sales table:
Line Total = Sales[Quantity] * Sales[Unit Price]
This column is computed once when the data refreshes, not every time a user interacts with the report. She then changed her measure to:
Total Sales = SUM(Sales[Line Total])
Now, the storage engine handles the SUM, which is much faster. The visual rendering time dropped from 12 seconds to 1.5 seconds.
But Sarah noticed another issue. The report had a page with 15 different visuals, each showing a different slice of the data. The page was slow to load because each visual sent its own query. She redesigned the report to use fewer, more versatile visuals. She replaced three separate bar charts with one visual that used bookmarks to switch between views. She also applied a technique called 'measure branching', where she created base measures (e.g., Total Sales, Total Cost) and then used those base measures in more complex measures (e.g., Profit Margin = DIVIDE([Total Sales] - [Total Cost], [Total Sales])). This prevented the engine from recalculating the same base sums multiple times.
Finally, Sarah checked the data model. She had imported a flat table with all columns in one table. She split it into a Sales fact table and separate dimension tables for Product, Store, and Date. She created relationships between them. Suddenly, filters like selecting a 'Store' no longer scanned the entire Sales table; they filtered the tiny Store dimension table first, and then the engine only scanned the relevant rows in Sales. The report became snappy even with the full 5 million rows.
In Sarah’s day-to-day job, she now follows a checklist:
Use variables in every measure to avoid repeated calculations
Replace iterators with stored calculated columns where possible
Always structure the model as a star schema
Use the Performance Analyser before delivering a report
Limit the number of visuals per page to 10-12
This approach turned her from someone who just builds reports into someone who builds reports that people actually use in real-time meetings.
The PL-300 exam tests your ability to identify and fix performance problems in DAX and data models. It does not ask you to write complex DAX from scratch. Instead, it gives you a scenario — a slow report, a poorly designed measure — and asks you to choose the best way to optimise it. Expect at least three to five questions on performance optimisation in the exam, spread across the new-style case studies and the multiple-choice sections.
The exam focuses heavily on the following topics:
Using variables (VAR) to improve readability and performance
Understanding the difference between storage engine and formula engine, and knowing which functions push work to the storage engine (SUM, COUNT, MIN, MAX) versus those that force the formula engine (SUMX, COUNTX, FILTER, ALL when used inside iterators)
Star schema design: recognising that a single flat table is a performance problem, and that splitting into fact and dimension tables is the recommended solution
Using CALCULATE to modify filter context efficiently, and knowing that CALCULATE is powerful but overusing nested CALCULATEs can degrade performance
The concept of 'query folding' in Power Query (though that is more for data transformation, the exam may link it to overall performance)
The role of the Performance Analyser pane and how to interpret its output (e.g., identifying which visuals have the highest duration)
Avoiding high-cardinality columns in your dimension tables because they increase memory usage and slow down filtering
The exam loves to set traps by offering you two measures that look similar but have vastly different performance. For example: Question: Which of the following measures is more optimised? Option A: Total Sales = SUMX(Sales, Sales[Quantity] * Sales[Unit Price]) Option B: Total Sales = SUM(Sales[Line Total]) The correct answer is Option B, because it avoids the row-by-row iteration. A trap is that Option A might work correctly, so beginners think both are fine. The exam wants you to know why Option A is slower.
Another common question type: You are given a report that is slow when a specific slicer changes. It asks you to recommend a solution. The answer is often to reduce the number of visuals on the page or to create a base measure that is reused rather than repeating calculations. Sometimes the answer is to change the data type of a column to reduce memory (e.g., storing dates as Date type instead of text reduces memory).
Key definitions to memorise:
Storage engine: fast, handles aggregation, does not handle complex logic
Formula engine: slower, handles iteration and complex expressions
VertiPaq: in-memory column-store engine that compresses data
Star schema: fact table surrounded by dimension tables
Measure branching: creating intermediate measures that are reused in other measures
Variables: VAR keyword stores intermediate results
Also be ready for questions on 'query folding' and 'data reduction', which are related topics. Know that query folding is when Power Query pushes transformation steps back to the source database, which is faster than transforming locally. For DAX, the equivalent is pushing calculations to the storage engine.
Finally, the exam will test your understanding of filter context and row context. A common trap question: Does a measure use filter context or row context? Correct answer: A measure always uses filter context. It does not have row context unless specifically invoked via an iterator. This distinction is crucial because using row context inside a measure without an iterator will cause an error or incorrect results.
Minimise row-by-row iteration by replacing SUMX and COUNTX with SUM and COUNT on precomputed columns wherever possible.
Always use variables (VAR) to store intermediate results and prevent repeated calculations within a single measure.
Design your data model as a star schema with one fact table and multiple dimension tables to maximise storage engine efficiency.
The storage engine is faster than the formula engine, so push as much work as possible to the storage engine by writing simple, aggregation-based measures.
Use the Performance Analyser pane in Power BI Desktop to identify which visuals or measures are causing delays in your report.
Measure branching — creating base measures and reusing them — reduces redundant computation and improves overall model performance.
Avoid using FILTER inside measures when a simpler CALCULATE filter expression will achieve the same result, because FILTER is an iterator.
Reduce the number of visuals per page and use bookmarks or drill-through pages to consolidate multiple views, decreasing the total number of queries sent to the engine.
These come up on the exam all the time. Here's how to tell them apart.
Measure (Calculated at query time)
Evaluated based on filter context from the visual or slicer
Does not consume memory for storage; computed on the fly
Cannot be used as a field in a slicer, only in measures or as values
Calculated Column (Calculated at refresh time)
Evaluated once per row when data is loaded or refreshed
Stored physically in the model, increasing file size and memory usage
Can be used as a field in a slicer, axis, or legend
SUMX (Iterator function)
Evaluates expression for each row in a table
Forces use of formula engine, which is slower
Flexible for row-level calculations without precomputation
SUM on calculated column (Simple aggregation)
Aggregates a single precomputed column across all rows
Can be handled entirely by storage engine, which is faster
Requires an extra calculated column to be added to the model
Flat table (Single table)
All data in one table; no relationships
High data redundancy and poor compression
Every filter scan reads the entire table, causing slower performance
Star schema (Fact + Dimension tables)
Single fact table linked to multiple dimension tables
Low redundancy; columns in dimensions are highly compressible
Filters applied to small dimension tables; fact table scanned only for relevant rows
FILTER (Function inside measure)
Iterates over entire table to evaluate condition row by row
Forces formula engine usage
Useful when filter condition is complex and cannot be expressed simply
CALCULATE with simple filter expression
Modifies filter context without per-row iteration
Can be handled by storage engine if filter expression is simple
Recommended for most filter scenarios due to better performance
Mistake
If my DAX formula gives the correct number, it is automatically the best formula to use.
Correct
Correctness is only the first criterion. Performance matters just as much. A formula that works but scans every row (like a naive SUMX) will be slow in large datasets. You must choose the most efficient formula, not just any correct one.
Most beginners have never experienced performance problems because they work with small datasets. They assume that correct output equals optimal code. The PL-300 exam explicitly tests performance, so this misconception causes many to choose the wrong answer.
Mistake
Adding calculated columns always improves performance because they store precomputed values.
Correct
Calculated columns are computed once at data refresh and then stored in memory, which can speed up measures. However, they also increase the model size because the column takes up memory. If you add many calculated columns, you can bloat the model and slow down refresh. Use them judiciously for expensive calculations, but not for everything.
Common sense says 'precompute = faster', but the trade-off of memory consumption is not obvious to beginners. They see the benefit without seeing the cost.
Mistake
Using the FILTER function inside a measure is always efficient because it filters the table before calculation.
Correct
FILTER iterates over every row of a table to check the condition, which can be very slow on large tables. The storage engine cannot compress FILTER operations efficiently. Often, using CALCULATE with a simple filter expression is faster because CALCULATE can leverage the storage engine's column scanning ability.
The word 'filter' sounds like it should reduce data, but FILTER is an iterator, not a simple filter. Beginners do not distinguish between filtering with CALCULATE (which pushes to the storage engine) and filtering with FILTER (which forces the formula engine to iterate).
Mistake
The Performance Analyser pane only helps with debugging errors, not with performance tuning.
Correct
The Performance Analyser pane is specifically designed to measure how long each visual takes to render. It shows the duration of DAX queries and display time. You can use it to identify the slowest visuals and then optimise their underlying measures.
Beginners often ignore the Performance Analyser because they do not know it exists or think it is for advanced developers. The exam expects you to know exactly how to use it to locate bottlenecks.
Mistake
A star schema is only for data warehouses and is not relevant for Power BI models.
Correct
Star schema is the recommended data model design for Power BI. It reduces data redundancy, improves compression, and speeds up queries because filters propagate from dimension tables to fact tables efficiently. Every Power BI report should ideally use a star schema.
Many beginners start with flat tables because they are easy to import. They do not know that this design leads to poor performance at scale. The exam directly tests this design principle.
Reveal each answer, then mark whether you got it right. Score 60%+ to unlock the next chapter.
A measure is a formula that is evaluated at query time based on the current filter context. It does not add data to the model. A calculated column is a formula that is evaluated once at data refresh and stored physically in the table, increasing the model size. Measures are better for dynamic aggregation; calculated columns are better for precomputing row-level values that are used repeatedly in measures.
SUMX is an iterator that loops through every row of the table it references, applying the expression row by row. This forces the formula engine to do the work, which is slower than a simple SUM that goes through the storage engine. If you can precompute the row-level calculation in a calculated column and then use SUM on that column, it will be much faster.
The Performance Analyser pane is a built-in tool in Power BI Desktop that records the time each visual takes to execute its DAX query and render. You open it from the View tab, click 'Start recording', interact with your report, and then review the log to identify which visuals are the slowest. You can then optimise those specific visuals.
CALCULATE can improve performance when used with simple filter expressions because it modifies filter context and can often be pushed to the storage engine. However, using nested CALCULATEs or complex filter arguments can actually degrade performance. The key is to use the simplest version of CALCULATE and avoid deeply nested logic.
A star schema is a database design with one central fact table (containing measurable numeric data) linked to several surrounding dimension tables (containing descriptive attributes). It is important for Power BI because it reduces data redundancy, improves data compression, and allows the storage engine to efficiently compute aggregations by scanning only relevant columns in the fact table, based on filters applied to the small dimension tables.
Measure branching is the practice of creating a simple base measure (e.g., Total Sales = SUM(Sales[Amount])) and then referencing that base measure in other, more complex measures (e.g., Profit Margin = DIVIDE([Total Sales] - [Total Cost], [Total Sales])). This reduces repeated calculation because the base measure is calculated once and reused, improving performance and simplifying maintenance.
The storage engine (VertiPaq) handles simple aggregations like SUM, COUNT, and MIN by scanning compressed column data in memory. It is very fast. The formula engine handles complex logic, row-by-row iteration, and functions like SUMX and FILTER. It is slower because it cannot compress as well. Optimised DAX pushes as much work as possible to the storage engine.
You've finished Advanced DAX and Query Optimization. Continue through the PL-300 study guide to build a complete picture of the exam.
Done with this chapter?