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 −

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 −

• Addition (+)
• 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 (+)

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).

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 (*)

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 (/)

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 −

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