ISBLANK
Returns TRUE when a value is blank.
ISBLANK(<value>)Returns: TRUE or FALSE (a scalar).
What it does
ISBLANK tests whether a value is blank, meaning there is no value at all. It is the correct test for missing data. A zero is not blank, so ISBLANK(0) returns FALSE. Use it inside IF, FILTER and CALCULATE to separate records that have a value from records that are missing one.
Syntax and arguments
- value: a column, measure or expression to test.
Example
The AP team wants to see how many vendor records are missing a tax ID, because those vendors cannot receive a 1099 form.
Vendors Missing Tax ID =
CALCULATE(
COUNTROWS(Vendors),
FILTER(
Vendors,
ISBLANK(Vendors[TaxID])
)
)
FILTER keeps only the vendor rows where the tax ID is blank, and CALCULATE counts them. A vendor with a tax ID of 0 would not be counted, because a 0 is not blank, so the test is for genuinely missing values.
Example: a second, different use
Use the blank test to switch between an actual and a forecast value:
Revenue For Report =
IF(
ISBLANK([Actual Sales]),
[Forecast Sales],
[Actual Sales]
)
If there are no actual sales yet for the period, the forecast is shown. Otherwise the actual figure is shown.
Things to watch
- A zero is not blank. Test for zero separately when the data uses zero to mean "missing."
- A measure that returns BLANK() makes ISBLANK true, so the test also works on measures.
- Test a column inside a row context, such as a FILTER or a calculated column. A bare column reference in a measure is not a single value.
- Some sources write empty text instead of a true blank. Check the model if the test misses rows you expect.
- Use COALESCE when you only need the first non-blank value, which is a shorter form of the same idea.
In business terms
ISBLANK is the basic data quality test: missing tax IDs, missing bank accounts, missing due dates, missing budget lines. Finance teams use it to build exception lists that must be cleared before a payment run or a close.