PowerPivot: DAX: Information Functions

PowerPivot: DAX: Information Functions

A Data Analysis Expressions (DAX) information function looks at the cell or row that is provided as an argument and tells you whether the value matches the expected type. For example, the ISERROR function returns TRUE if the value that you reference contains an error.

Read more about the following information functions:

Original article (before wiki edits) was written by Jeannine Takaki and JuanPablo Jofre and formatted by Mary Browning, Microsoft SQL Server Technical Writers.


ISBLANK

Checks whether a value is blank, and returns TRUE or FALSE.

Returns TRUE if the value is blank; otherwise FALSE (BOOL).

Syntax

ISBLANK(<value>)

Value. The value or expression you want to test.

Example

The formula shown after the table computes the increase or decrease ratio in sales compared to the previous year. The example uses the IF function to check the value for the previous year's sales in order to avoid a divide by zero error.

Row Labels Total Sales Total Sales Previous Year Sales to Previous Year Ratio
2001

$10,209,985.08

2002

$28,553,348.43

$10,209,985.08

179.66%
2003

$39,248,847.52

$28,553,348.43

37.46%
2004

$24,542,444.68

$39,248,847.52

-37.47%
Grand Total

$102,554,625.71

Formula: //Sales to Previous Year Ratio

=IF( ISBLANK('CalculatedMeasures'[PreviousYearTotalSales])
   , BLANK()
   , ( 'CalculatedMeasures'[Total Sales]-'CalculatedMeasures'[PreviousYearTotalSales] )
      /'CalculatedMeasures'[PreviousYearTotalSales])

Back to top


ISERROR

Checks whether a value is an error, and returns TRUE or FALSE.

Returns TRUE if the value is an error; otherwise FALSE (BOOL).

Syntax

ISERROR(<value>)

Value. The value you want to test.

Example

The following example calculates the ratio of total Internet sales to total reseller sales. The ISERROR function is used to check for errors, such as division by zero. If there is an error a blank is returned, otherwise the ratio is returned.

= IF( ISERROR(
       SUM('ResellerSales_USD'[SalesAmount_USD])
       /SUM('InternetSales_USD'[SalesAmount_USD])
             )
    , BLANK()
    , SUM('ResellerSales_USD'[SalesAmount_USD])
      /SUM('InternetSales_USD'[SalesAmount_USD])
    )

Back to top


ISLOGICAL

Checks whether a value is a logical value, (TRUE or FALSE), and returns TRUE or FALSE.

Returns TRUE if the value is a logical value; FALSE if any value other than TRUE OR FALSE.

Syntax

ISLOGICAL(<value>)

Value. The value you want to test.

Examples

The following examples show the behavior of ISLOGICAL:

//RETURNS: Is Boolean type or Logical
=IF(ISLOGICAL(true), "Is Boolean type or Logical", "Is different type")

//RETURNS: Is Boolean type or Logical
=IF(ISLOGICAL(false), "Is Boolean type or Logical", "Is different type")

//RETURNS: Is different type
=IF(ISLOGICAL(25), "Is Boolean type or Logical", "Is different type")

Back to top


ISNONTEXT

Checks if a value is not text (blank cells are not text), and returns TRUE or FALSE.

An empty string is considered text.

Returns TRUE if the value is not text or blank; FALSE if the value is text.

Syntax

ISNONTEXT(<value>)

Value. The value you want to test.

Examples

The following examples show the behavior of the ISNONTEXT function:

//RETURNS: Is Non-Text
=IF(ISNONTEXT(1), "Is Non-Text", "Is Text")

//RETURNS: Is Non-Text
=IF(ISNONTEXT(BLANK()), "Is Non-Text", "Is Text")

//RETURNS: Is Text
=IF(ISNONTEXT(""), "Is Non-Text", "Is Text")

Back to top


ISNUMBER

Checks whether a value is a number, and returns TRUE or FALSE.

Returns TRUE if the value is numeric; otherwise FALSE. 

Syntax

ISNUMBER(<value>)

Value. The value you want to test.

Examples

The following examples show the behavior of ISNUMBER:

//RETURNS: Is number
=IF(ISNUMBER(0), "Is number", "Is Not number")

//RETURNS: Is number
=IF(ISNUMBER(3.1E-1),"Is number", "Is Not number")

//RETURNS: Is Not number
=IF(ISNUMBER("123"), "Is number", "Is Not number")

Back to top


ISTEXT

Checks if a value is text, and returns TRUE or FALSE.

Returns TRUE if the value is text; otherwise FALSE

Syntax

ISTEXT(<value>)

Value. The value you want to test.

Examples

The following examples show the behavior of the ISTEXT function:

//RETURNS: Is Text
=IF(ISTEXT("text"), "Is Text", "Is Non-Text")

//RETURNS: Is Text
=IF(ISTEXT(""), "Is Text", "Is Non-Text")

//RETURNS: Is Non-Text
=IF(ISTEXT(1), "Is Text", "Is Non-Text")

//RETURNS: Is Non-Text
=IF(ISTEXT(BLANK()), "Is Text", "Is Non-Text")

Back to top


See Also


External Links

Leave a Comment
  • Please add 3 and 5 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • Ed Price MSFT edited Revision 9. Comment: I elongated the DAX link at the top for consistency and to make it more obvious.

  • Ed Price MSFT edited Revision 8. Comment: Added TOC anchor and horizontal lines.

  • Ed Price MSFT edited Original. Comment: Added DAX link at top.

Page 1 of 1 (3 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • Ed Price MSFT edited Original. Comment: Added DAX link at top.

  • Ed Price MSFT edited Revision 8. Comment: Added TOC anchor and horizontal lines.

  • Ed Price MSFT edited Revision 9. Comment: I elongated the DAX link at the top for consistency and to make it more obvious.

Page 1 of 1 (3 items)