# Excel DAX - Data Types

Table is a new data type in Data Model.

• You can use a table containing multiple columns and multiple rows of data as an argument to a DAX function.

• Some DAX functions return tables, which are stored in memory and can be used as arguments to other DAX functions.

Datetime is another new data type in Data Model that is used for date and time values. DAX functions that require date and/or time as arguments, require the datetime data type.

## Summary of DAX Data Types

Following are the data types supported by DAX −

Sr.No Data Type & Description
1

A 64 bit (eight-bytes) integer value

Numbers that have no decimal places.

Integers can be positive or negative numbers, but must be whole numbers between 9,223,372,036,854,775,808 (-2^63) and 9,223,372,036,854,775,807 (2^63-1).

2

A 64 bit (eight-bytes) real number

Real numbers are numbers that can have decimal places. Real numbers cover the following values −

• Negative values from -1.79E +308 through 2.23E -308

• Zero

• Positive values from 2.23E -308 through 1.79E + 308

However, the number of significant digits is limited to 15 decimal digits.

3

Boolean

• True
• False
4

String

A Unicode character data string.

Can be strings, numbers or dates represented in a text format.

Maximum string length is 268,435,456 Unicode characters (256 mega characters) or 536,870,912 bytes.

5

datetime

Dates and times in an accepted date-time representation.

Valid dates are all dates after January 1, 1900.

6

Currency

Currency data type allows values between 922,337,203,685,477.5808 to 922,337,203,685,477.5807 with four decimal digits of fixed precision.

7

Blank

A blank is a data type in DAX that represents and replaces SQL nulls. You can create a blank by using the BLANK function, and test for blanks by using the logical function, ISBLANK.

8

Table

Represents a table in the data model.

Note − Data Model does not support the use of the variant data type used in Excel. Hence, when you load or import data, ensure that the data in each column of a table is of consistent data type.

DAX functions work with the following data types −

• Scalar values, including strings.
• Numbers, both integers and real numbers.
• Dates and times.

DAX functions return error in case of data type incompatibility. Ensure that the data type of each column in a table is as required. If not, you can explicitly set the data type from the Ribbon in the Power Pivot window.

You can learn about the data types required for each DAX function in the tutorial - DAX Functions in this tutorials library.

## Implicit Data Type Conversion in DAX Formulas

You do not need to cast, convert, or otherwise specify the data type of a column or a value that you use in a DAX formula. When you use data in a DAX formula, DAX automatically identifies the data types in referenced columns and of the values that you type in and performs implicit conversions where necessary to complete the specified operation.

However, there are some limitations on the values that can be successfully converted. If a value or a column has a data type that is incompatible with the current operation, DAX returns an error.

Example of implicit data conversion in DAX − Suppose you have a DAX formula wherein you are adding a number to a Date. DAX interprets it in the context of function that is used. Both the arguments are converted to a common data type and the result is returned in the intended data type.

The type of conversion that is performed is determined by the operator, which casts the values it requires before performing the requested operation. In the following sections, you can find the tables of implicit data conversions for the following operators −

• Subtraction (-)
• Multiplication (*)
• Division (/)
• Comparison Operators

Each of the 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.

### Implicit Data Conversion Table for Addition (+)

Operator (+) INTEGER CURRENCY REAL DATETIME
INTEGER INTEGER CURRENCY REAL DATETIME
CURRENCY CURRENCY CURRENCY REAL DATETIME
REAL REAL REAL REAL DATETIME
DATETIME DATETIME DATETIME DATETIME DATETIME

For example, if A is of Currency data type and B is a Real data type, then while calculating A+B, DAX converts A into Real and adds it to B. The result will be a Real data type.

### Implicit Data Conversion Table for Subtraction (-)

The row header is the minuend (left side) and the column header is the subtrahend (right side).

Operator (-) INTEGER CURRENCY REAL DATETIME
INTEGER INTEGER CURRENCY REAL REAL
CURRENCY CURRENCY CURRENCY REAL REAL
REAL REAL REAL REAL REAL
DATETIME DATETIME DATETIME DATETIME DATETIME

For example, if A is of Integer data type and B is of Real data type, then while calculating A-B, DAX converts A into Real and subtracts B from A. The result will be a Real data type.

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

### Implicit Data Conversion Table for Multiplication (*)

Operator (*) INTEGER CURRENCY REAL DATETIME
INTEGER INTEGER CURRENCY REAL INTEGER
CURRENCY CURRENCY REAL CURRENCY CURRENCY
REAL REAL CURRENCY REAL REAL

For example, if A is of Real data type and B is of Currency data type, then to calculate A*B, DAX converts A to Currency data type and multiplies A with B. The result will be a Currency data type.

### Implicit Data Conversion Table for Division (/)

Operator (/) INTEGER CURRENCY REAL DATETIME
INTEGER REAL CURRENCY REAL REAL
CURRENCY REAL CURRENCY REAL REAL
REAL REAL REAL REAL REAL
DATETIME REAL REAL REAL REAL

For example, if A is Currency data type and B is of Real data type, then while calculating A/B, DAX will convert A to Real number and performs the division. The result will be a Real data type.

### Implicit Data Conversion Table for Comparison Operators

In comparison expressions, DAX follows the rules mentioned below −

• Boolean values are considered greater than string values.

• String values are considered greater than numeric or datetime values.

• Numbers and datetime values are considered to have the same rank.

• No implicit data conversions are performed for Boolean or string values.

• BLANK or a blank value is converted to 0/""/false depending on the data type of the other compared value.

• For numeric or datetime types, data conversions are performed implicitly as shown in the following table −

Comparison Operator INTEGER CURRENCY REAL DATETIME
INTEGER INTEGER CURRENCY REAL REAL
CURRENCY CURRENCY CURRENCY REAL REAL
REAL REAL REAL REAL REAL
DATETIME REAL REAL REAL DATETIME

## How DAX Handles Blanks, Empty Strings, and Zero Values?

In DAX, a null, a blank value, an empty cell, or a missing value are all represented by the same special value, a BLANK.

• You can generate a BLANK with the DAX BLANK function.
• You can test for a BLANK by using the DAX ISBLANK function.

How DAX handles blanks in DAX formulas or DAX functions depends on the individual operation such as addition or concatenation or the specific DAX function.

### Examples

DAX Formula Result
BLANK + BLANK BLANK
BLANK + 5 5
BLANK * 5 BLANK
5 / BLANK Infinity
0 / BLANK NaN
BLANK / BLANK BLANK
FALSE OR BLANK FALSE
FALSE AND BLANK FALSE
TRUE OR BLANK TRUE
TRUE AND BLANK FALSE
BLANK OR BLANK BLANK
BLANK AND BLANK BLANK
excel_dax_syntax.htm