BLANK
Returns a blank value, which means no value at all.
BLANK()Returns: BLANK (a scalar with no value).
What it does
BLANK returns a blank: the absence of a value. It is not zero and it is not an empty string. Measures that return BLANK are hidden in most visuals, which is useful when a result does not apply, such as a margin for a month with no sales. Use BLANK() to say "there is no answer here" instead of showing a misleading zero.
Syntax and arguments
BLANK takes no arguments. Write BLANK() with brackets.
Example
The margin measure should show nothing for a product category that had no sales in the month, not a margin of zero, which would wrongly suggest a break-even month.
Gross Margin % =
IF(
[Total Sales] = 0,
BLANK(),
DIVIDE([Gross Margin], [Total Sales])
)
When the month has no sales, the measure returns BLANK, and the chart shows a gap. When sales exist, the margin percentage is shown. A zero would have pulled the trend line down, so BLANK is the honest answer.
Example: a second, different use
Clear a value a user should not see, such as a salary for a restricted group:
Visible Salary =
IF(
USERPRINCIPALNAME() = "hr.lead@example.com",
Payroll[Salary],
BLANK()
)
Only the named user sees the value. Everyone else sees a blank. For real security, use row level security in the model, because a measure can be bypassed in some tools.
Things to watch
- BLANK is not zero. A sum ignores a BLANK, but a comparison treats BLANK as zero, so test carefully.
- BLANK does not make the report fail. It tells the visual to skip the value.
- Charts draw a gap for BLANK, which can look like a missing feed. Explain the gap on the page.
- Use BLANK() as the else value when you want the default to be "nothing" in SWITCH or IF.
- Hiding data with BLANK in a measure is not security. Use row level security for that.
In business terms
BLANK is how a report says "not applicable" without a fake number. It is the right result for a ratio with no denominator, a metric before a launch date and a figure for a period that is still open.