Ten DAX mistakes beginners make (and the fixes)

Most beginner DAX mistakes fall into three groups: formulas that return wrong numbers (multiplying totals, summing ratios, counting the wrong thing), formulas that hide problems (bare division, IFERROR, blanks forced to zero), and formulas that are slow or fragile (calculated columns everywhere, FILTER over whole tables, repeated expressions). Each has a simple fix.

None of these produce an error message, which is why they survive until someone checks a total against the finance system. If DAX is new to you, start with our plain-English explainer on DAX. The fixes below follow Microsoft's DAX guidance on Microsoft Learn, checked as of September 2026.

Which DAX mistakes give you wrong numbers?

1. Multiplying two totals instead of iterating

Your sales table has Quantity and Unit Price, but no line value. The tempting measure is:

Revenue = SUM ( Sales[Quantity] ) * SUM ( Sales[Unit Price] )

On a single order line it is right. On anything bigger it multiplies total quantity by the sum of every unit price, which means nothing. The calculation has to happen per row, then be added up — that is what the X functions are for:

Revenue = SUMX ( Sales, Sales[Quantity] * Sales[Unit Price] )

SUMX walks the table row by row, multiplies, then sums the results.

2. Storing a ratio in a calculated column

A calculated column Margin % = DIVIDE ( Sales[Profit], Sales[Revenue] ) gives a correct percentage on every row. Drop it onto a visual and Power BI sums or averages those percentages, so the regional and grand totals are wrong — an average of row margins is not the margin of the total.

The fix is a measure that divides the totals:

Margin % = DIVIDE ( SUM ( Sales[Profit] ), SUM ( Sales[Revenue] ) )

A measure is re-evaluated in every cell, including the total row, so the total becomes total profit over total revenue. The total is never an addition of the rows; it is the same formula, run again with fewer filters.

3. Counting a column when you mean rows

COUNT ( Sales[OrderID] ) counts non-blank values in that column. If any rows have a blank OrderID, the count is quietly short. Microsoft's COUNTROWS guidance recommends that when you intend to count table rows, you "always use the COUNTROWS function": it does not consider blanks in any column, and it performs better.

Order Lines = COUNTROWS ( Sales )

4. Time intelligence without a proper date table

SAMEPERIODLASTYEAR and TOTALYTD assume a date table with one row per day and no gaps. Point them at the order date column in your fact table, where dates repeat and days with no orders simply do not appear, and the results can be incomplete or wrong.

Microsoft's date table documentation says you have to mark your date table if you use the classic time intelligence functions, and marking it makes Power BI check that the date column contains unique values, no null values and contiguous dates. Its newer calendar-based time intelligence removes that need in most cases. Either way, build a dedicated date table and relate it to your facts — our star schema guide covers why Auto date/time is not a substitute.

Which DAX mistakes hide problems instead of fixing them?

These look like defensive coding, but mostly make the model slower and errors harder to find.

5. Dividing with / instead of DIVIDE

[Profit] / [Revenue] does not cope with a zero or blank revenue: instead of an empty cell, the report shows infinity or NaN. Microsoft's DIVIDE guidance is to use DIVIDE "whenever the denominator is an expression that could return zero or BLANK". With no alternate result supplied, DIVIDE returns BLANK, and Microsoft notes it is better optimised for testing the denominator than an IF wrapper.

Margin % = DIVIDE ( [Profit], [Revenue] )

The exception: dividing by a constant, such as [Revenue] / 1000 for a thousands display. There the plain operator is the recommended choice.

6. Wrapping formulas in IFERROR

IFERROR ( [Profit] / [Revenue], BLANK () ) works, and it is the pattern Microsoft's error functions guidance tells you to avoid: ISERROR and IFERROR increase the number of storage engine scans a query needs. It also hides why the error happened.

Microsoft's own replacement for that pattern is simply DIVIDE ( [Profit], [Revenue] ). More generally, most evaluation errors come from unexpected blanks, zeros or bad type conversions, so fix them where they start: clean invalid or missing values in Power Query before they reach the model, and use IF to test a condition rather than catching the error it causes.

7. Turning blanks into zeros

A measure like this looks tidy:

Sales Shown = IF ( ISBLANK ( [Total Sales] ), 0, [Total Sales] )

By default, report visuals drop any grouping whose value is BLANK, which is why a customer table shows only customers who bought something. Force a zero and every customer appears, zeros and all. Microsoft's guidance on converting blanks calls these designs inefficient, says they turn a sparse calculation into a dense one that uses more memory, and recommends measures return BLANK when no meaningful value exists.

If a card must show "0" rather than "(Blank)", handle it on that one visual, not in the measure every other visual uses.

Which DAX mistakes make reports slow or fragile?

8. Calculated columns for everything

Coming from Excel, a new column feels natural. But in an Import model a calculated column is computed at refresh and stored for every row, and Microsoft's calculated columns documentation notes that materialised columns "can negatively impact refresh performance".

A workable rule: if it changes the shape of a table, do it in Power Query; if it is a number on a report, write a measure. Keep calculated columns for things you genuinely slice, group or relate by, such as an order-size banding.

9. FILTER over a whole table inside CALCULATE

This is how many people first learn to filter a measure:

Red Sales = CALCULATE ( [Total Sales], FILTER ( 'Product', 'Product'[Colour] = "Red" ) )

It iterates the entire Product table. Microsoft's guidance on FILTER as a filter argument recommends Boolean expressions as filter arguments "whenever possible", because Import tables are in-memory column stores built to filter columns, not scan tables:

Red Sales = CALCULATE ( [Total Sales], 'Product'[Colour] = "Red" )

Two things to know. A Boolean filter overwrites any existing filter on that column, so if a slicer on colour should still apply, wrap it: KEEPFILTERS ( 'Product'[Colour] = "Red" ). And Boolean filters cannot reference a measure or columns from more than one table — that is when FILTER is the right tool.

10. Repeating the same expression instead of using variables

A year-on-year measure written the long way calculates last year's sales twice:

Sales YoY % =
DIVIDE (
    [Total Sales] - CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) ),
    CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
)

With a variable, it is calculated once and has a name:

Sales YoY % =
VAR SalesPriorYear =
    CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
RETURN
    DIVIDE ( [Total Sales] - SalesPriorYear, SalesPriorYear )

Microsoft's variables guidance says variables can improve performance, reliability and readability, because the repeated version makes Power BI evaluate the same expression twice. One trap to know about: variables are evaluated outside the filters your RETURN expression applies. A variable defined before a CALCULATE is not affected by that CALCULATE's filters.

What does each mistake look like at a glance?

#	Mistake	Symptom	Fix
1	Multiplying two sums	Totals far too large	SUMX over the table
2	Ratio in a calculated column	Totals and subtotals wrong	Measure dividing the totals
3	COUNT on a column	Counts slightly low	COUNTROWS
4	No proper date table	Year-on-year gaps or blanks	Dedicated, related date table
5	/ operator	Infinity or NaN when the denominator is zero	DIVIDE
6	IFERROR wrappers	Slow, errors hidden	Clean data, IF, DIVIDE
7	Blanks forced to zero	Huge tables, slow visuals	Let measures return BLANK
8	Calculated columns everywhere	Big model, slow refresh	Measures, or Power Query
9	FILTER over a table	Slow filtered measures	Boolean filter, KEEPFILTERS
10	Repeated expressions	Slow, hard to read	VAR … RETURN

How do you check a measure before you trust it?

This is our routine rather than a Microsoft rule.

1. Reconcile one total to the source. Pick a month and check the measure against the finance system or the raw export.
2. Put the measure in a table visual with its total row. A ratio whose total is the rows added up is mistake 2; a product whose total is far too large is mistake 1.
3. Filter to one item you can verify by hand. One customer, one week. If you cannot work out the right answer yourself, you cannot tell whether DAX got it.
4. Check the references. Microsoft's references guidance is to always fully qualify columns (Sales[Revenue]) and never qualify measures ([Total Sales]), so readers can tell which is which and moving a measure to another table breaks nothing.

Our DAX keyboard shortcuts help once you write measures daily. But most of these mistakes are modelling problems wearing a DAX costume: a clean model makes DAX short. If your reports rest on formulas nobody wants to touch, our Power BI consultancy work usually starts by rebuilding the model underneath, and the wider data and analytics practice covers where that fits.

Frequently asked questions

What is the most common DAX mistake?

In our experience, using a calculated column where a measure belongs. A calculated column is worked out once per row and stored; a measure is worked out for whatever the report is filtering. Ratios, totals and anything that should respond to a slicer belong in a measure, and putting them in a column is behind most totals that do not add up.

Should I use DIVIDE or the / operator in DAX?

Use DIVIDE whenever the denominator could be zero or BLANK. It returns BLANK in that case, or an alternate result you supply, and Microsoft says it is better optimised for testing the denominator than an IF function. When the denominator is a constant, such as dividing by 1,000, the / operator is the recommended choice.

Why does my DAX total not match the sum of the rows?

Because the total row is not an addition of the rows above it. Power BI evaluates the measure again in the total row's own filter context. For a ratio, that gives the ratio of the totals, which is usually what you want; for a formula that multiplies two sums, it gives a meaningless number. Use SUMX when a calculation has to happen row by row before it is added up.

Do I need to mark a date table in Power BI?

If you use the classic time intelligence functions, yes. Microsoft's documentation says you have to mark the date table in that case, and marking it checks that the date column holds unique values, no nulls and contiguous dates. Its newer calendar-based time intelligence removes the need in most cases.

Is IFERROR bad in DAX?

It is best avoided. Microsoft's guidance says ISERROR and IFERROR increase the number of storage engine scans a query needs, and they hide the cause of the error. Fix the data in Power Query, test the condition with IF, or use an error-tolerant function such as DIVIDE instead.

---

Easy Insight is a UK consultancy for AI, web, apps and data — senior specialists only, no juniors.