The worst DAX bugs don’t throw errors. The formula validates, the report refreshes, everything looks fine, and the number is just wrong. No red squiggle, no warning, nothing pointing you at the cause. You usually find out when someone in a meeting taps the screen and asks why that figure doesn’t match the source.
I’ve lost more hours to that kind of silent failure than to any actual error message. Here are the five that catch people most often, and what to do instead.
Reserved keywords as variable names
DAX won’t let you name a variable after a reserved word or an existing table. Some names, such as Date and Value, also clash with function names. That makes formulas hard to read and easy to break.
The fix is dull and it works: prefix every variable with a lowercase v. vStatus, not Status. Once it’s a habit you stop thinking about it.
VAR vStatus = SELECTEDVALUE('Table'[Status_Column])
VAR vClass = SELECTEDVALUE('Table'[Class_Column])
RETURN
IF(ISBLANK(vStatus), "Unknown", vStatus)
Chains of = comparisons instead of a list membership check
When you want to check whether a value is one of several options, a chain of vType = "Express" || vType = "Priority" comparisons is the obvious move. It’s long, it’s easy to get wrong, and = treats a BLANK as equal to 0 or “”. Use a list membership check instead.
vType IN {"Express","Priority","Same-Day"} and CONTAINSROW(vList, vType) do the same thing. Both use strict comparison, so a BLANK won’t match 0 or “”. Pick one and keep the list in a variable:
VAR vList = {"Express", "Priority", "Same-Day"}
RETURN
SWITCH(TRUE(),
CONTAINSROW(vList, vType), "Fast",
"Standard"
)
The list lives in one place, and adding a value is a one-line change.
Not handling blanks before you compare
Blanks cause more wrong-but-not-broken results than anything else here. A check like vStatus = "Active" does something unhelpful when vStatus is blank, and the row ends up in the wrong bucket without a word of warning.
Catch blanks first, before any other comparison:
SWITCH(TRUE(),
ISBLANK(vStatus), "No Status", -- blanks first, always
vStatus = "Active", "Live",
"Other"
)
Make that your opening branch and everything below it can assume it’s working with a real value. Most of my classification logic starts this way now, out of pure self-preservation.
Using SELECTEDVALUE in the wrong place
In a calculated column, reference the column directly. The row context gives you the current row.
VAR vRevenue = 'Sales'[Revenue]
SELECTEDVALUE() is for measures. There it reads what the filter context, such as a slicer or a matrix row, has narrowed to. It returns BLANK when more than one value is in play. Use it in a calculated column and it sees the whole column, so you get BLANK on every row. It’s the usual reason a formula works fine as a measure but breaks as a column.
Dividing without DIVIDE()
The classic. [Covered] / [Total] works perfectly right up until Total is zero. Then the visual quietly shows Infinity or NaN instead of a number.
DIVIDE() exists for this exact reason:
RETURN DIVIDE(vCovered, vTotal)
It handles the zero gracefully: blank by default, or a value you nominate. I can’t think of a good reason to use the / operator directly in a measure, except dividing by a constant (say / 100). There Microsoft recommends /, because it can’t fail and it’s faster.
How do you stop DAX failing silently?
None of these fixes are clever. They’re careful. Assume values can be blank. Assume a list needs a real membership check. Assume division will hit zero eventually, because it will. Good DAX is mostly defensive DAX. You’re writing for the messy data you’ll actually get, not the clean data someone promised you in the kickoff meeting.
These silent failures are also a common reason two measures or visuals in the same report disagree. When your Power BI metrics conflict, check these five first.
Get these into muscle memory and the whole silent-failure category mostly goes away. That’s a decent chunk of your week back.
Originally published at alanconlon.com, where I post practical security, Azure and data write-ups every other Tuesday.
Top comments (0)