BLANK vs 0 in DAX: How to Control Empty Results

In DAX, BLANK() usually means there is no meaningful value in the current context; 0 is a real numeric result. They can look similar in a visual, but they affect chart density, averages, conditional formatting, and business interpretation differently. Convert blank to zero only when zero is the truthful answer.

Why DAX returns blank

A measure can return blank because:

• no fact rows match the current filters;
• a denominator is zero or blank;
• a lookup finds no matching value;
• SELECTEDVALUE sees zero or multiple values;
• an explicit IF branch returns BLANK(); or
• a relationship does not propagate the expected filter.

Start by finding the cause. Formatting blank as zero can hide a broken relationship or unexpected filter context.

BLANK and 0 answer different questions

Suppose a store had no Sales rows in February:

• BLANK() can mean “there was no observation.”
• 0 can mean “we observed the store and confirmed zero sales.”

Those meanings affect averages. If January sales were 100 and February was not observed, the average of observed months is 100. If February was observed and sales were zero, the two-month average is 50.

The model and requirement—not a visual preference—must decide which interpretation applies.

Use DIVIDE for safe ratios

Margin % =
DIVIDE ( [Margin], [Total Sales] )

When the denominator is zero or blank, DIVIDE returns blank by default. You can provide an alternate constant:

Margin %, Show Zero =
DIVIDE ( [Margin], [Total Sales], 0 )

The alternate result is appropriate only if “no sales” should display as a zero margin percentage. In many reports, blank is more honest because the ratio is undefined. See Microsoft’s DIVIDE reference.

Use COALESCE for an intentional fallback

Sales Display =
COALESCE ( [Total Sales], 0 )

COALESCE returns the first expression that is not blank. It is clearer than adding zero:

-- Avoid as a communication pattern
[Total Sales] + 0

Both may produce the desired display, but COALESCE tells the next developer that a blank fallback is intentional.

Keep calculation and display measures separate

A useful pattern is to preserve semantic blank behavior in the base measure:

Total Sales =
SUM ( Sales[Sales Amount] )

Then create a display-specific measure:

Total Sales, Show Zero =
COALESCE ( [Total Sales], 0 )

Use the base measure in other calculations and the display measure only where the report requirement explicitly needs zero. This prevents an early fallback from changing later averages or counts.

SELECTEDVALUE blank is not a numeric zero

Selected Year =
SELECTEDVALUE ( 'Date'[Year] )

This returns blank unless exactly one year is visible. Replacing that with zero produces a fake year, not a helpful fallback.

For display text, use a descriptive alternate:

Selected Year Label =
SELECTEDVALUE ( 'Date'[Year], "Multiple years" )

If this function is surprising you, follow the seven SELECTEDVALUE checks.

Visual behavior changes

Power BI often suppresses categories where every measure is blank. Converting the measure to zero can cause the visual to show every Date, Product, or Customer member, making a sparse chart much denser.

Before adding COALESCE, test:

• table and matrix row counts;
• line and column chart categories;
• “Show items with no data” settings;
• conditional-formatting rules;
• average and percentile measures; and
• exported data.

A prettier visual is not worth changing the meaning of the calculation.

Diagnose the context first

Create temporary measures:

Visible Sales Rows =
COUNTROWS ( Sales )

Visible Product Count =
COUNTROWS ( VALUES ( Product[ProductKey] ) )

Sales State =
SWITCH (
    TRUE (),
    ISBLANK ( [Total Sales] ), "BLANK",
    [Total Sales] = 0, "ZERO",
    "VALUE"
)

These distinguish no result from a genuine zero. If there should be Sales rows but the row count is zero, inspect relationships and filters rather than changing the output.

A practical decision table

Return blank when:

• the ratio is undefined;
• no observation exists;
• a selection is ambiguous;
• the value is not applicable; or
• showing a point would imply false precision.

Return zero when:

• the entity and period were observed;
• the business definition says no matching activity equals zero;
• downstream arithmetic needs a real zero and that behavior is documented; or
• a display requirement explicitly includes empty categories as zero.

Verification checklist

• What does blank mean in this model?
• What does zero mean to the report reader?
• Is the blank caused by missing rows, a denominator, a selection, or a relationship?
• Will zero change averages or ratios?
• Will the visual show additional categories?
• Should the fallback live in a display measure?
• Have you tested a genuine zero and a no-row case separately?

Do not use + 0 as a universal cleanup step. Preserve meaning first, then choose the display. Test the difference in the DAX playground.