In DAX, you define the data type for columns of data in a table. In this section, we will look at the different data types that are available and delve into the implicit data type conversions that take place when data is used in a DAX expression. We will also look at the different groups of available operators.

Choosing the correct data type when building your data model helps to ensure that the size of your model is kept to a minimum. It can also help with performance when it comes to refreshing the data in your model.

When you load new data into your model, the modeling engine will attempt to pick the most efficient data type for a column, based on the values that it is importing for that column. However, it is worth checking the data types that it selects, as it may not always choose the most appropriate data type for your data needs. For example, if a column currently contains only integer numbers, the modeling engine will pick the Whole Number data type. If this column subsequently contains fractional values, then the fractional part of these numbers will be lost when the data is imported. Worse still, if the column subsequently contains non-numeric data in the column, then you will get errors when the data is refreshed.

Implicit conversions are described later in this article. Table 1-2 gives details of the different data types available in DAX:

DAX functions have specific requirements for the type of data used for inputs and outputs. If the data in a column passed as an argument is not compatible with the data type required by the function, DAX will try to implicitly convert it into the required data type. If this is not possible, it will return an error.

The type of implicit conversion that DAX performs is determined by the operator, it will convert the data into the type required before it performs the requested operation. Tables 1-3 through to 1-6 list the operators and show the implicit conversion that takes place when the data type in the row is combined with the data type in the column.

Table 1-3 shows the implicit conversion that takes place when a value with the data type in the row is added to a value with the data type in the column:

Table 1-4 shows the implicit conversion that takes place when a value with the data type in the row is subtracted from a value with the data type in the column:

Table 1-5 shows the implicit conversion that takes place when a value with the data type in the row is multiplied by a value with the data type in the column:

Table 1-6 shows the implicit conversion that takes place when a value with the data type in the row is divided by a value with the data type in the column:

In addition to the implicit conversions of numeric types shown in the preceding tables, DAX will automatically convert numbers into strings and strings into numbers depending on the requirements of the operator.

For the concatenation operator (&), DAX will convert numeric values into string values:

Measure 1-1 = 2 & 3

In this example, the DAX measure will evaluate to the string value, "23".

For an arithmetic operator such as addition (+), string values will be converted into a numeric value where possible:

Measure 1-2 = "2" + "3"

In this example, the DAX measure will evaluate to the numeric value 5.

However, there is the potential for errors to occur when allowing for automatic conversion as described. For example, where you are passing string values to an arithmetic operator that cannot be converted into a number, your expression will generate an error. Therefore, you must ensure that correct data types are used for the columns that are used with operators. Exception handling should be done if there is any possibility of errors occurring.

There are four groups of operators in DAX:

• Arithmetic
• Comparison
• Concatenation
• Logical

Table 1-7 shows the different types of operator available within the arithmetic group, along with an example illustrating typical use:

When using arithmetic operators it is important to consider the order in which they need to be applied. If necessary, use parentheses to override the precedence of an operator. Table 1-8 shows the order of precedence for each of the different DAX arithmetic operators:

The following gives an example of where parenthesis can be used to override the precedence of an operator:

5*2+6 = 16

Here, the 5 is multiplied by the 2 to give 10, before the 6 is added to give 16. The multiplication operator (*) has higher precedence than the addition operator (+), so that part of the calculation is calculated first.

However, take a look at this example:

5*(2+6) = 40

Here, the use of the parentheses around 2+6 gives it higher precedence and causes it to be calculated before the result is multiplied by 5.

Table 1-9 shows the different types of comparison operators available, with an example illustrating the operator being used:

Table 1-10 shows the concatenation operator, with some examples illustrating how it is used:

It is important to note that, as we have seen in the previous section on data types, when using the concatenation operator, DAX will implicitly convert numeric values to string values.

Table 1-11 shows the different types of logical operators available, with examples of each operator being used:

In addition to the preceding logical operators, DAX also has the logical AND and OR functions that replicate the functionality of the AND operator (&&) and OR operator (||) respectively.

The advantage of using these functions over the equivalent operators in a complex expression is that it is easier to format and read the code. However, one drawback is that the functions only accept two arguments, restricting you to comparing two conditions only. To be able to compare multiple conditions, you will need to nest the functions. In this case, it might be better to use the AND operator (&&) instead.

The following gives an example of the syntax for the AND function:

Measure 1-3 =
IF (
    AND (
        20 > 10,
        -20 < -10
    ),
    "All true",
    "One or more false"
)

The following gives an example showing the syntax of the AND function nested to compare three conditions:

Measure 1-4 =
IF (
    AND (
        AND (
            10 > 9,
            5 < 10
        ),
        20 > 10
    ),
    "All true",
    "One or more false"
)

The following gives an alternative example of the one given, using the equivalent AND operator (&&):

Measure 1-5 =
IF (
    10 > 9
        && 5 < 10
        && 20 > 10,
    "All true",
    "One or more false"
)

Any column in a table can have blank values, which are the result of the data source containing NULL in values. How a blank value affects the result of a DAX expression depends on the data type expected and the operator being used. In some instances, a blank value will be converted into a zero or an empty string, while in others, it will propagate through as a blank. Table 1-12 shows how different DAX operators handle blank values:

The BLANK data type represents nulls, blank values, empty cells, and missing values. The BLANK function is used to generate blanks, while the ISBLANK function is used to verify a blank value.