When a DAX measure returns BLANK, first determine whether the base
rows are absent, the aggregation is blank, or the final formula
deliberately produces blank. Do not start with
COALESCE ( [Measure], 0 ); that can hide a broken
relationship, missing prior period, or invalid business state.
Use this diagnostic flow from the model outward.
Step 1: Count
the rows available to the measure
Create a temporary measure against the fact table:
Debug Fact Rows =
COUNTROWS ( Sales )Place it in the same visual and filter state as the blank
measure.
- If the count is zero, investigate filter context and
relationships. - If rows exist, inspect the input column and aggregation.
- If the count changes correctly but the measure stays blank, debug
the formula branch by branch.
This is more useful than staring at the final expression.
Step 2: Test the base
aggregation
Suppose the measure is:
Margin Percent =
DIVIDE ( [Margin], [Sales Amount] )Add [Margin] and [Sales Amount] to the
visual separately.
Then go one level lower:
Debug Sales Amount =
SUM ( Sales[SalesAmount] )Debug Nonblank Amount Rows =
COUNT ( Sales[SalesAmount] )Rows can exist while the numeric column contains only blanks. A row
count and a nonblank-value count answer different questions.
Step 3: Expose the
current filter context
Show what the measure can see:
Debug Categories =
CONCATENATEX (
VALUES ( Product[Category] ),
Product[Category],
", "
)Debug Date Range =
FORMAT ( MIN ( 'Date'[Date] ), "yyyy-mm-dd" )
& " to "
& FORMAT ( MAX ( 'Date'[Date] ), "yyyy-mm-dd" )Use the exact columns referenced by the visual and slicer. A field
that looks equivalent may come from a disconnected table or a different
date role.
If the same measure works in a card but fails in a matrix row, the
matrix adds filter context that removes all matching rows or sends the
formula down a different branch.
Step 4: Trace the
relationship path
A dimension filter reaches the fact table only through active
relationships.
Test:
Debug Visible Products =
COUNTROWS ( VALUES ( Product[ProductKey] ) )Debug Visible Sales Rows =
COUNTROWS ( Sales )Change a Product slicer.
- If visible products change but Sales rows do not, the relationship
path is broken or intentionally disconnected. - If both change to zero, the selection may have no matching fact
rows. - If Sales rows change correctly, the blank is probably inside the
measure.
Use the Power BI
relationship checklist before adding virtual relationships or
bidirectional filtering.
Step 5: Check
single-value assumptions
SELECTEDVALUE returns its alternate result when the
context contains zero or more than one distinct value:
Selected Currency =
SELECTEDVALUE ( Currency[Code] )Debug it with:
Debug Currency Count =
COUNTROWS ( VALUES ( Currency[Code] ) )If the count is not one, the blank is expected.
Provide an explicit alternate only when the business rule supports
it:
Selected Currency =
SELECTEDVALUE ( Currency[Code], "Multiple" )Do not use a fallback that silently chooses one currency. The SELECTEDVALUE returns blank
guide covers all seven checks.
Step 6: Check division and
arithmetic
DIVIDE returns BLANK by default when its denominator is
zero or blank:
Margin Percent =
DIVIDE ( [Margin], [Sales Amount] )That is often safer than forcing zero. A zero margin percentage says
there was a valid denominator and no margin; BLANK can say the
percentage is undefined.
If the report specification requires zero:
Margin Percent Display =
COALESCE (
DIVIDE ( [Margin], [Sales Amount] ),
0
)Keep the semantic measure and display decision conceptually separate.
Microsoft
recommends retaining BLANKs when they efficiently exclude irrelevant
groupings, rather than converting every missing result to a dense
zero.
Step 7: Inspect IF and
SWITCH branches
A measure can return BLANK because no branch matches:
Status Value =
SWITCH (
SELECTEDVALUE ( Status[Name] ),
"Open", [Open Amount],
"Closed", [Closed Amount]
)With no final else expression, unmatched states return BLANK.
Add a temporary diagnostic:
Status Value Debug =
SWITCH (
SELECTEDVALUE ( Status[Name] ),
"Open", [Open Amount],
"Closed", [Closed Amount],
-999999
)If the sentinel appears, the issue is branch selection. Remove the
sentinel after testing.
Also inspect nested IF conditions,
HASONEVALUE, ISINSCOPE, and variables that
capture a blank earlier than expected.
Step 8: Verify date coverage
Time-intelligence measures return BLANK when the requested comparison
period contains no dates or no fact rows.
For prior-year sales:
Sales Last Year =
CALCULATE (
[Sales Amount],
SAMEPERIODLASTYEAR ( 'Date'[Date] )
)Check:
- whether the date table includes the prior-year dates;
- whether Sales has matching rows;
- whether the Date relationship is active;
- whether the visual uses the Date table; and
- whether another filter removes the comparison period.
Use the focused SAMEPERIODLASTYEAR blank
checklist for that case, or the date-table guide
for broader time-intelligence failures.
Step 9: Find
filters the measure removes or adds
The measure may create an impossible intersection:
Red Bike Sales =
CALCULATE (
[Sales Amount],
Product[Color] = "Red",
Product[Category] = "Bikes"
)If the model contains no red bikes, BLANK is correct.
Or a nested measure may remove the context needed for a lookup:
CALCULATE (
[Some Measure],
REMOVEFILTERS ( Customer )
)Expose filters with VALUES, count fact rows after each
modifier, and add one filter argument at a time.
Step 10:
Decide whether BLANK is the correct result
BLANK is often meaningful:
- no rows match;
- a ratio is undefined;
- no single selection exists;
- no prior period exists;
- a hierarchy level should not display the measure;
- the calculation is not applicable.
Only convert BLANK to zero when zero is a truthful numeric statement.
The BLANK vs 0 guide provides the
decision rules.
A compact diagnostic measure
set
Keep these temporary measures nearby:
Debug Rows = COUNTROWS ( Sales )Debug Keys = COUNTROWS ( VALUES ( Sales[OrderKey] ) )Debug Min Date = MIN ( 'Date'[Date] )Debug Max Date = MAX ( 'Date'[Date] )Debug Selection Count =
COUNTROWS ( VALUES ( Selector[Value] ) )They answer five questions: Are rows present? At what grain? In which
period? Under how many selections?
Frequently asked questions
Why
does my measure return BLANK in a table but work in a card?
The table row adds filter context. That context may remove all
matching fact rows, produce multiple values where one was expected, or
trigger an ISINSCOPE/IF branch.
Why does adding 0 make
the value appear?
Arithmetic or COALESCE converts BLANK to a numeric
result. That changes display behavior but does not explain why the
original measure was blank.
Can a broken relationship
cause BLANK?
Yes. A filter can fail to reach the fact table, or a transitioned key
can match no rows. Count visible fact rows under the same dimension
selection.
Where can I isolate the
failure?
Paste the measure and its base dependencies into the DAX
playground. Keep the row-count and selection-count measures visible
until the first blank-producing step is identified.
