Microsoft SQL Server 2012 Analysis Services: DAX Basics

Now that you have seen the basics of SQL Server Analysis Services (SSAS) Tabular, it is time to learn the fundamentals of Data Analysis Expressions (DAX) expressions. DAX has its own syntax for defining calculation expressions; it is somewhat similar to a Microsoft Excel expression, but it has specific functions that enable you to create more advanced calculations on data stored in multiple tables.

Understanding Calculation in DAX

Any calculation in DAX begins with the equal sign, which resembles the Excel syntax. Nevertheless, the DAX language is very different from Excel because DAX does not support the concept of cells and ranges as Excel does; to use DAX efficiently, you must learn to work with columns and tables, which are the fundamental objects in the Tabular world.

Before you learn how to express complex formulas, you must master the basics of DAX, which include the syntax, the different data types that DAX can handle, the basic operators, and how to refer to columns and tables. In the next few sections, we introduce these concepts.

DAX Syntax

A relatively simple way to understand how DAX syntax works is to start with an example. Suppose you have loaded the FactInternetSales table in a Tabular project. In Figure 4-1, you can see some of its columns.

Figure 4-1 Here you can see the FactInternetSales table in a Tabular project.

You now use this data to calculate the margin, subtracting the TotalProductCost from the SalesAmount, and you use the technique already learned in Chapter 2, “Getting Started with the Tabular Model,” to create calculated columns. To do that, you must write the following DAX formula in a new calculated column, which you can call GrossMargin.

= FactInternetSales[SalesAmount] - FactInternetSales[TotalProductCost]

This new formula is repeated automatically for all the rows of the table, resulting in a new column in the table. In this example, you are using a DAX expression to define a calculated column. You can see the resulting column in Figure 4-2. (Later, you see that DAX is used also to define measures.)

Figure 4-2 The GrossMargin calculated column has been added to the table.

This DAX expression handles numeric values and returns a numeric value. DAX can work with data types other than numbers. In the next section, you learn the different data types available in DAX and how to work with them.

DAX Data Types

DAX can compute values for seven data types:

• Integer

• Real

• Currency

• Date (datetime)

• TRUE/FALSE (Boolean)

• String

• BLOB (binary large object)

DAX has a powerful type-handling system so that you do not have to worry much about data types. When you write a DAX expression, the resulting type is based on the type of the terms used in the expression and on the operator used. Type conversion happens automatically during the expression evaluation.

Be aware of this behavior in case the type returned from a DAX expression is not the expected one; in such a case, you must investigate the data type of the terms used in the expression. For example, if one of the terms of a sum is a date, the result is a date, too. However, if the data type is an integer, the result is an integer. This is known as operator overloading, and you can see an example of its behavior in Figure 4-3, in which the OrderDatePlusOne column is calculated by adding 1 to the value in the OrderDate column, by using the following formula.

= FactInternetSales[OrderDate] + 1

The result is a date because the OrderDate column is of the date data type.

Figure 4-3 Adding an integer to a date results in a date increased by the corresponding number of days.

In addition to operator overloading, DAX automatically converts strings into numbers and numbers into strings whenever it is required by the operator. For example, if you use the & operator, which concatenates strings, DAX automatically converts its arguments into strings. If you look at the formula

= 5 & 4

it returns a “54” string result. However, the formula

= "5" + "4"

returns an integer result with the value of 9.

As you have seen, the resulting value depends on the operator and not on the source columns, which are converted following the requirements of the operator. Even if this behavior is convenient, later in this chapter you see the types of errors that might occur during these automatic conversions.

= NOW() + 1

DAX Operators

You have seen the importance of operators in determining the type of an expression; you can now see, in Table 4-1, a list of the operators available in DAX.

Table 4-1 Operators

Moreover, the logical operators are available also as DAX functions, with syntax very similar to Excel syntax. For example, you can write these conditions

AND( [Country] = "USA", [Quantity] > 0 )
OR( [Country] = "USA", [Quantity] > 0 )
NOT( [Country] = "USA" )

that correspond, respectively, to

[Country] = "USA" && [Quantity] > 0
[Country] = "USA" || [Quantity] > 0
!( [Country] = "USA" )

DAX Values

You have already seen that you can use a value directly in a formula, for example, USA or 0, as previously mentioned. When such values are used directly in formulas, they are called literals and, although using literals is straightforward, the syntax for referencing a column needs some attention. Here is the basic syntax.

'Table Name'[Column Name]

The table name can be enclosed in single quote characters. Most of the time, quotes can be omitted if the name does not contain any special characters such as spaces. In the following formula, for example, the quotes can be omitted.

TableName[Column Name]

The column name, however, must always be enclosed in square brackets. Note that the table name is optional. If the table name is omitted, the column name is searched in the current table, which is the one to which the calculated column or measure belongs. However, we strongly suggest that you always specify the complete name (table and column) when you reference a column to avoid any confusion.