Performance and testing

01The problem

Kolanut's report has grown to five pages and forty measures. The customer page takes eight seconds to load, and the sales director has started exporting to Excel instead. Worse, last week a measure showed ₦76.6m for supermarket sales in one visual and ₦78.0m in another, and nobody could say which was right.

A report that's slow doesn't get used, and a report that's wrong does damage. On 4,266 rows almost anything is fast, but the habits in this lesson are what keep a model fast at 40 million rows, and the testing routine is what lets you say "this number is right" with confidence.

02The concept

Find the slow part first

Optimize → Performance Analyzer → Start recording, then refresh the visuals. Each visual's time is split into:

• DAX query: the time the engine spent calculating. This is the part your measures control.
• Visual display: drawing. Too many points, or too many visuals on one page.
• Other: waiting for other visuals.

Copy a slow visual's query into DAX query view to run and change it on its own.

DAX habits that keep measures fast

The model matters as much as the DAX. Remove columns nobody uses, and reduce the number of distinct values (split a date-time into a date and a time; round long decimals). The engine compresses columns, and fewer distinct values compress far better.

Testing measures

Before a report goes out, test each important measure:

1. Reconcile with the source. Pick a total and check it outside Power BI: in SQL, Excel or the source system.
2. Test at every level. Check a row, a subtotal and the grand total. Ratios and distinct counts often don't add up across rows, and they shouldn't. Make sure the total means what the reader will think it means.
3. Test the edges. A period with no sales, a customer with one order, a filter that leaves nothing.
4. Compare two routes to the same number. Gross Revenue − Discount Amount should equal Revenue.

DAX query view makes these checks fast:

EVALUATE
ROW (
    "Order lines", COUNTROWS ( orders ),
    "Revenue", [Revenue],
    "Check", [Gross Revenue] - [Discount Amount] - [Revenue]
)

Check should be 0.

03Example

The ₦76.6m versus ₦78.0m mystery. One visual used:

Supermarket Revenue = CALCULATE ( [Revenue], customers[channel] = "Supermarket" )

The other used a copy of the formula pasted months earlier, before discounts were part of revenue:

Supermarket Revenue (old) =
CALCULATE (
    SUMX ( orders, orders[quantity] * orders[unit_price] ),
    FILTER ( orders, RELATED ( customers[channel] ) = "Supermarket" )
)

The old version is wrong (it ignores discounts) and slow (it filters the whole orders table row by row). For January to June 2026, a SQL query on the source gives ₦76,556,135 of supermarket revenue after discounts, which reconciles with the first measure. The fix is the same as in lesson 1: one base measure, reused, and no pasted copies.

04Walkthrough

1. Open Performance Analyzer, start recording and click Refresh visuals. Expand the slowest visual and note its DAX query time.
2. Click Copy query on that visual, paste it into DAX query view and run it.
3. Run the EVALUATE ROW test from the concept section. Check that Order lines is 4,266 and Check is 0.
4. Run a reconciliation query for supermarket revenue in January to June 2026:

EVALUATE
SUMMARIZECOLUMNS (
    customers[channel],
    TREATAS ( { 2026 }, 'Date'[Year] ),
    "Revenue", [Revenue]
)

5. Search your measures for FILTER ( orders and IFERROR, and rewrite each one using the table above.

05Practice

North Revenue = CALCULATE(SUMX(orders, orders[quantity] * orders[unit_price] * (1 - orders[discount_pct] / 100)), FILTER(orders, RELATED(customers[region]) = "North Central" || RELATED(customers[region]) = "North West"))

06More practice

07Check your understanding