Data types in Data Models (2024)

In a Data Model, each column has an associated data type that specifies the type of data the column can hold: whole numbers, decimal numbers, text, monetary data, dates and times, and so on. Data type also determines what kinds of operations you can do on the column, and how much memory it takes to store the values in the column.

If you’re using the Power Pivot add-in, you can change a column’s data type. You might need to do this if a date column was imported as a string, but you need it to be something else. For more information, see Set the data type of a column in Power Pivot.

In this article

• Summary of data types

  • Table Data Type

• Implicit and explicit data type conversion in DAX formulas

  • Table of Implicit Data Conversions

    • Addition (+)

    • Subtraction (-)

    • Multiplication (*)

    • Division (/)

    • Comparison operators

• Handling blanks, empty strings, and zero values

  See Also

  Enter data manually in worksheet cellsConvert a data type - Microsoft SupportProgramming Concepts Course Notes - Data TypesString data type

Summary of data types

The following table lists data types supported in a Data Model. When you import data or use a value in a formula, even if the original data source contains a different data type, the data is converted to one of these data types. Values that result from formulas also use these data types.

1 DAX formulas do not support data types smaller than those listed in the table.

2 If you try to import data that has very large numeric values, import might fail with the following error:

In-memory database error: The '<column name>' column of the '<table name>' table contains a value, '1.7976931348623157e+308', which is not supported. The operation has been cancelled.

This error occurs because Power Pivot uses that value to represent nulls. The values in the following list are synonyms for the null value:

Remove the value from your data and try importing again.

Implicit and explicit data type conversion in DAX formulas

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

  See Also

  The Types of Cell Elements

• 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. The following expression returns 44: = "22" + 22

• If you attempt to concatenate two numbers, Excel will present them as strings and then concatenate.The following expression returns "1234": = 12 & 34

The following table summarizes the implicit data type conversions that are performed in formulas. Excel performs implicit conversions whenever possible, as required by the specified operation.

Handling blanks, empty strings, and zero values

In DAX, a null, blank value, empty cell, or a missing value are all represented by the same new value type, a BLANK. You can also generate blanks by using the BLANK function, or test for blanks by using the ISBLANK function.

How blanks are handled in operations, such as addition or concatenation, depends on the individual function. The following table summarizes the differences between DAX and Microsoft Excel formulas, in the way that blanks are handled.

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

Top of Page