Skip to content
Tutorials Center
My learning

Topics

Lesson 28 of 56 ยท DAX

Debugging and Optimizing DAX

Find and fix DAX problems: write EVALUATE queries in DAX query view, record Performance Analyzer, use DAX Studio, split logic with variables and avoid common mistakes.

A DAX measure that returns the wrong number is frustrating, because the formula looks fine and the report gives you no error. A measure that is right but slow is just as common: the visual spins for ten seconds and users give up. Both problems have the same cure, which is to stop guessing and look at what the engine is doing.

This lesson gives you a working process. First you run the measure in isolation with EVALUATE, then you split it into variables to see the pieces, then you measure how long it takes with the Performance Analyzer and DAX Studio, and finally you check the usual suspects. The tools are part of Power BI Desktop and cannot run here, so the DAX is read-only. The Python examples are stand-ins that show the same ideas: tracing intermediate values, spotting a wrong aggregation and understanding why some patterns cost so much more than others. The lesson builds on CALCULATE and Filter Context and Common DAX Patterns.

A debugging routine

Isolate Run the measure in a query.
Split Break it into variables.
Measure Time it, find the slow part.
Fix Change one thing, test again.

Change one thing at a time, or you will not know what helped.

1 / 2

DAX query view and EVALUATE

Power BI Desktop has a DAX query view (one of the view icons at the left edge, next to Report, Table and Model, as described in A Tour of Power BI Desktop). It is a text editor connected to your model where you can run queries. A query is not a measure: it returns a table, and it always starts with EVALUATE.

The simplest query returns a whole table:

DAX
EVALUATE 'Product'

More useful is a query that returns your measure the same way a visual would, grouped by some columns:

DAX
EVALUATE
SUMMARIZECOLUMNS(
    'Product'[Category],
    "Revenue", [Total Revenue],
    "Margin %", [Margin %]
)
ORDER BY 'Product'[Category]

SUMMARIZECOLUMNS is the function that visuals use behind the scenes, so the numbers match what a table visual shows. This is the quickest way to test a measure without clicking around a report. You can also test a filter context directly, for example only 2025 and only one region:

DAX
EVALUATE
ROW(
    "Revenue 2025 North",
    CALCULATE(
        [Total Revenue],
        'Date'[Year] = 2025,
        Customer[Region] = "North"
    )
)

Trying a measure before saving it

A DEFINE block lets you write a draft measure that lives only inside the query. Nothing changes in the model until you choose to update it, so it is a safe sandbox:

DAX
DEFINE
    MEASURE Sales[Margin % draft] =
        DIVIDE([Total Profit], [Total Revenue])
EVALUATE
SUMMARIZECOLUMNS(
    'Product'[Category],
    "Draft", [Margin % draft]
)

When the draft behaves, the query view offers to add it to the model, or you copy it to a real measure. The view also has quick queries in the context menu of a table or column (such as showing the top rows), which are good for looking at your data.

Variables as a debugger

DAX has no step-through debugger, but variables give you something close. Rewrite a long expression so that each stage has a name:

DAX
Share of Month =
VAR TotalRevenue = [Total Revenue]
VAR MonthRevenue =
    CALCULATE([Total Revenue], 'Date'[Month Number] = 2)
VAR Share = DIVIDE(MonthRevenue, TotalRevenue)
RETURN
    Share

To inspect a stage, change the RETURN line to return a different variable (RETURN MonthRevenue) and look at the result. Variables are evaluated once and reused, which also makes the measure faster. They are evaluated in the filter context where they are defined, not where they are used, and that is a frequent source of confusion: a variable defined outside CALCULATE does not see the new filters inside it.

The stand-in below mimics that technique. Each VAR logs its value, so you can see every stage, and you could change what is returned at the end.

Python Tracing variables like a debugger

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

With Total at 700 and ThisMonth at 400, the share is 0.571 (400 divided by 700). If the printed Total had been 400, you would know at once that something filtered the total when it should not have, which is the bug this technique is best at finding.

The most common mistakes

Most wrong numbers come from a short list.

  • Average of ratios instead of ratio of totals. A calculated column that stores a margin per row, averaged in a visual, weights a tiny order the same as a huge one. A measure should divide the totals.
  • Totals that do not add up. A measure is calculated again for the total row, with no row filter. A measure that is right per row can look wrong in the total if it is not additive, such as a distinct count or a ratio. That is usually correct behavior, not a bug.
  • Blank versus zero. A missing value shows as blank, and a blank hides the row in a visual. Adding + 0 to a measure makes every combination appear, which can bloat a visual with empty rows.
  • Wrong context. A variable captured outside CALCULATE, an active relationship that is not the one you expected (see Relationships, Cardinality and Filter Direction), or a filter direction that stops propagating.
  • Division by zero. Use DIVIDE(a, b) and not a / b, so a zero denominator returns blank and no error.
  • Date table problems. Time intelligence needs a proper, marked date table with no gaps, covered in Date Tables and Time Intelligence Setup.

The next example shows the first one with numbers.

Python Average of ratios vs ratio of totals

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Both regions sell 1,000 in revenue, yet the averaged ratios say 0.35 and 0.375 while the true margins are 0.34 and 0.30. The grand total shows 0.36 against the true 0.32. The correct measure is DIVIDE([Total Profit], [Total Revenue]), which works at every level.

Finding what is slow

Once the numbers are right, check speed. Power BI has three levels of tooling.

Performance Analyzer. On the View ribbon, choose Performance analyzer, then Start recording and Refresh visuals. For each visual you see how long it took, split into DAX query, Visual display and Other. If the DAX query dominates, the measure or model is the problem. If display dominates, the visual is too heavy (too many points or columns). Copy query places the exact query on the clipboard; paste it into DAX query view to test and tune it. Repeat the recording a couple of times, because the first run after opening the file is often slower than later ones.

DAX Studio. This free external tool connects to the open model and adds what Desktop lacks. Server Timings splits a query into storage engine time (fast, multi-threaded scans of the compressed columns) and formula engine time (slow, single-threaded evaluation of your DAX logic). A good query spends most of its time in the storage engine. A heavy formula engine share, or many storage-engine queries, points to iterators and complicated filters. Query Plan shows the logical and physical plan.

Model-level tools. Tools such as Tabular Editor with a Best Practice Analyzer, or the VertiPaq Analyzer view in DAX Studio, show which columns use the most memory. Removing unused, high-cardinality columns often speeds up everything.

What makes DAX slow

  • Nested iterators. SUMX over a large table with a FILTER or another iterator inside it compares row by row, and the work grows with the square of the rows.
  • Filtering a whole table. FILTER(Sales, ...) iterates every fact row, while CALCULATE([Total], Sales[Region] = "North") lets the storage engine do it. Filter a column when you can, not a table.
  • Many-to-many and bi-directional relationships, and high-cardinality columns in visuals.
  • Repeating the same expression. Store it in a variable once.
  • Complex measures in a card on many pages, since each visual is a separate query.

The Python example below is a stand-in for the nested-iterator case. It ranks values two ways, with a comparison against every other value (like a FILTER per row) and with a single sort, and counts the steps. The step counts are a rough measure of work, not a timing.

Python Why nested iteration costs so much

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Both approaches give the same answer, but with 1,000 values the nested version needs a million comparisons against about two thousand. DAX's own engine is far smarter than this plain loop, but the lesson carries over: the cost of a pattern depends on how its work grows, so test a measure on realistic data volumes, not just the sample.

Make one change per test and record the Performance Analyzer time before and after. A faster visual after three simultaneous changes teaches you nothing about which change mattered.

A short checklist

When a number looks wrong: run the measure in a query with SUMMARIZECOLUMNS, split it into variables, return each in turn and compare with a quick pandas or Excel check. When a visual is slow: record it in the Performance Analyzer, paste the query into DAX Studio, look at storage-engine versus formula-engine time, then remove nested iterators and unused columns. Next, you move from numbers to pictures in Choosing the Right Visual.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which keyword must a query in DAX query view start with to return a table?

    A DAX query returns a table through EVALUATE. DEFINE is optional and only declares measures or variables used by the query.
  2. 2. A margin column is stored per row and a visual averages it. Why can the result be misleading?

    An average of ratios differs from the ratio of totals. A measure like DIVIDE of the profit total by the revenue total weights each order by its size.
  3. 3. In Performance Analyzer, what does a large DAX query time tell you?

    The DAX query time is spent in the engine. A large visual display time would point to rendering.
  4. 4. In DAX Studio server timings, which is the usual sign of an expensive query?

    The storage engine is fast and parallel. Heavy formula-engine work, which runs single-threaded, usually means iterators or complex filters.

Summary

  • Run a measure in isolation with EVALUATE SUMMARIZECOLUMNS(...) in DAX query view; use DEFINE MEASURE to try drafts safely.
  • Variables are the closest thing to a debugger: return each one in turn to see a stage.
  • Typical wrong numbers come from averaging ratios, wrong filter context, blank versus zero and unmarked date tables.
  • Performance Analyzer splits a visual's time into DAX query, display and other; DAX Studio splits the query into storage and formula engine.
  • Avoid nested iterators and filtering whole tables, store repeated expressions in variables, and change one thing at a time.

Next: DAX Text Functions: FORMAT, CONCATENATEX, SUBSTITUTE and More, the first of five lessons on the DAX function families, followed by Choosing the Right Visual.