Data Types in Data Analysis Expressions

[This topic is pre-release documentation and is subject to change in future releases. Blank topics are included as placeholders.]

This section describes the data types that can be used in Data Analysis Expressions (DAX) formulas, and discusses the implicit conversion of data types when data is calculated or used in a DAX formula.

Data Types used in DAX Formulas

The following data types are supported for use in DAX formulas. When you use a value in a formula, even if the original data source contains a different data type, the data is cast to one of the following data types. Values that result from formulas also use these data types.

In general, these data types are implemented to enable accurate calculations in calculated columns, and the same restrictions apply to the rest of the client for consistency.

Data type	Description
I8 (eight-byte integer)	Numbers that have no decimal places. Integers can be positive or negative numbers, but must be whole numbers.
R8 (eight-byte real number), or double	Real numbers. A real number may have many decimal places. The number of digits after the decimal is not defined.
Boolean	False and true, or 1 and 0.
String	Character data. Can be strings, or numbers and dates represented in a text format.
Date (datetime)	Dates and times in an accepted date-time representation.
CY (currency)	Currency data
Blank	A blank is a data type in DAX that represents and replaces SQL nulls and empty strings. You can create a blank by used the BLANK function, and test for blanks by using the logical function, ISBLANK.

DAX formulas do not support data types that are smaller than those listed in the table. Any attempt to define a calculated column as being of a smaller type, such as I4, will result in an error.

Note

In general, formats used for numbers, currency, dates and times should follow the format of the locale that is specified on the computer that opens the workbook. You can use the formatting options in the worksheet to control the way that the value is displayed.

New Table Data Type

In addition, DAX uses a table data type. This data type is used by DAX in many functions, such as aggregations and time intelligence calculations. Some functions require a reference to a table; other functions return a table that can then be used as input to other functions.

Note

This list is not comprehensive and is subject to change in later versions of the product.

Category	Functions that return a table	Functions that require a table as input
Aggregation and filtering	ALL ALLEXCEPT CALCULATETABLE DISTINCT FILTER RELATEDTABLE VALUES	AVERAGEX COUNTAX COUNTX MAXX MINX SUMX

Note

In some functions that require a table as input, you can specify an expression that evaluates to a table; for some functions, a reference to a base table is required. For information about the requirements of specific functions, see Function Reference (DAX).

Implicit and Explicit Data Type Conversion

Each DAX function has specific requirements as to the types of data that are used as inputs and outputs. For example, some functions require integers for some arguments and dates for others; other functions require text or tables.

If the data in the column that you specify as an argument is incompatible with the data type required by the function, DAX in many cases will return an error. However, wherever possible DAX will attempt to implicitly convert the data to the required data type. For example:

• You can type a date as a string, and DAX will parse the string and attempt to cast it as one of the Windows date and time formats.
• You can add TRUE + 1 and get the result 2, because TRUE is implicitly converted to the number 1 and the operation 1+1 is performed.
• If you add values in two columns, and one value happens to be represented as text ('12') and the other as a number 12), DAX implicitly converts the string to a number and then does the addition for a numeric result.
• If you attempt to concatenate two numbers, the code name PowerPivot add-in will present them as strings and then concatenate.

The following table summarizes the implicit data type conversions that are performed in formulas. In general, PowerPivot behaves like Microsoft Excel, and performs implicit conversions whenever possible when required by the specified operation.

Conversions are also performed implicitly when comparing the values in columns. When comparing column values, DAX first converts values to a common type. Therefore, comparisons such as ="12" = 12 always return TRUE.

Table of Implicit Data Conversions

The type of conversion that is performed is determined by the operator, which casts the values it requires before performing the requested operation. These tables list the operators, and indicate the conversion that is performed on each data type in the column when it is paired with the data type in the intersecting row.

Note

Text data types are not included in these tables. When a number is represented as in a text format, in some cases PowerPivot will attempt to determine the number type and represent it as a number.

Addition (+)

Operator (+)	I8	CY	R8	Date
I8	I8	CY	R8	Date
CY	CY	CY	R8	Date
R8	R8	R8	R8	Date
Date	Date	Date	Date	Date

For example, if a real number (R8) is used in an addition operation in combination with currency data (CY), both values are converted to R8, and the result is returned as R8.

Subtraction (-)

Operator (-)	I8	CY	R8	Date
I8	I8	CY	R8	R8
CY	CY	CY	R8	R8
R8	R8	R8	R8	R8
Date	Date	Date	Date	Date

For example, if a date is used in a subtraction operation with any other data type, both values are converted to dates, and the return value is also a date.

Note

PowerPivot also supports the unary operator, - (negative), but this operator does not change the data type of the operand.

Multiplication (*)

Operator (*)	I8	CY	R8	Date
I8	I8	CY	R8	I8
CY	CY	R8	CY	CY
R8	R8	CY	R8	R8

For example, if an integer (I8) is combined with a real number (R8) in a multiplication operation, both numbers are converted to real numbers (R8), and the return value is also R8.

Division (/)

Operator (/)	I8	CY	R8	Date
I8	I8	CY	R8	I8
CY	CY	R8	CY	CY
R8	R8	CY	R8	R8

For example, if an integer is combined with a currency value in a division operation, both values are converted to currency (CY), and the result is also a currency value.

Handling of Blanks, Empty Strings, and Zero Values

The way that DAX handles zero values, nulls, and empty strings is different from both Microsoft Excel and SQL Server. This section describes the differences, and describes how these data types are handled.

The important thing to remember is that, in PowerPivot, a blank value, an empty cell, or a missing value are all represented by the same new value type, a BLANK. How blanks are handled in operations, such as addition or concatenation, depends on the individual function. You can also generate blanks by using the BLANK function, or test for blanks by using the ISBLANK function.

Database nulls are not supported within a PowerPivot workbook, and nulls are implicitly converted to blanks when a column that contains a null value is referenced in a DAX formula.

Defining Blanks, Nulls and Empty Strings

The following table summarizes the differences between DAX and in Microsoft Excel, in the way that blanks are handled.

Expression	DAX	Excel
BLANK + BLANK	BLANK	0 (zero)
BLANK +5	5	5
BLANK * 5	BLANK	0 (zero)
5/BLANK	Infinity	Error
0/BLANK	NaN	Error
BLANK/BLANK	BLANK	Error
FALSE OR BLANK	FALSE	Error
FALSE AND BLANK	FALSE	Error
TRUE OR BLANK	TRUE	Error
TRUE AND BLANK	FALSE	Error
BLANK OR BLANK	BLANK	Error
BLANK AND BLANK	BLANK	Error

For details on how a particular function or operator handles blanks, see the individual topics for each DAX function, in the section, Function Reference (DAX).

Note

Sometimes a calculated column might appear blank even though it should have values. This can happen when the data in the column is invalidated. A column can be invalidated when the calculation depends on another column that has invalid data, or when the calculation cannot be performed correctly because something has changed, or the operation is not permitted, such as division by zero.

See Also

Other Resources

Key Concepts in DAX
Creating Expressions

Help and Information

Getting SQL Server 2008 R2 Assistance