ISNUMBER
Returns TRUE when a value is a number.
ISNUMBER(<value>)Returns: TRUE or FALSE (a scalar).
What it does
ISNUMBER tests the data type of a value. It returns TRUE for a number, including zero, and FALSE for text, for a blank and for an error. Use it to check that an amount column really holds numbers before you total it, or to separate a clean row from one that needs a conversion.
Syntax and arguments
- value: the value to test, a column reference or an expression.
Example
A bank import sometimes places notes such as "pending" in the amount column. The reconciliation must count only the rows where the amount is a real number.
Amount Is Number = ISNUMBER(BankLines[Amount])
Rows with a numeric amount return TRUE, and rows with a note return FALSE. A filter on this column gives the list of rows to review before the match runs.
Example: a second, different use
Count the numeric entries in a column of imported balances, to measure how clean the import was:
Numeric Balances =
CALCULATE(
COUNTROWS(Balances),
FILTER(
Balances,
ISNUMBER(Balances[Amount])
)
)
The count of numeric rows, compared with the total row count, shows the share of the import that can be used without conversion.
Things to watch
- A blank is not a number, so ISNUMBER(blank) is FALSE. Test for blanks separately with ISBLANK.
- A number stored as text, such as "1234", is text, so ISNUMBER returns FALSE. Convert it with VALUE first.
- A data type in the model is usually a number, so ISNUMBER will be TRUE for most rows. It matters most for imported text columns.
- Do not use ISNUMBER to test for dates. Dates are stored as numbers in DAX, so the result may surprise you.
- ISNUMBER does not check that the number is right, only that it is a number.
In business terms
ISNUMBER is a data quality test for imported ledgers, bank files and survey results. It finds the rows that will break a total or a comparison, so the team can fix them before the close.