Microsoft SQL Server 2012 Analysis Services: DAX Basics

Measures

You have already seen in Chapter 2 how to create a measure by using the measure grid; now you learn the difference between a calculated column and a measure to understand when to use which one.

Calculated columns are easy to create and use. You have already seen in Figure 4-2 how to define the GrossMargin column to compute the amount of the gross margin.

[GrossMargin] = FactInternetSales[SalesAmount] - FactInternetSales[TotalProductCost]

But what happens if you want to show the gross margin as a percentage of the sales amount? You could create a calculated column with the following formula.

[GrossMarginPerc] = FactInternetSales[GrossMargin] / FactInternetSales[SalesAmount]

This formula computes the right value at the row level, as you can see in Figure 4-4.

Figure 4-4 The GrossMarginPerc column shows the Gross Margin as a percentage, calculated row by row.

Nevertheless, when you compute the aggregate value, you cannot rely on calculated columns. In fact, the aggregate value is computed as the sum of gross margin divided by the sum of sales amount. Thus, the ratio must be computed on the aggregates; you cannot use an aggregation of calculated columns. In other words, you compute the ratio of the sum, not the sum of the ratio.

The correct formula for the GrossMarginPerc is as follows.

= SUM( FactInternetSales[GrossMargin] ) / SUM( FactInternetSales[SalesAmount] )

But, as already stated, you cannot enter it into a calculated column. If you need to operate on aggregate values instead of on a row-by-row basis, you must create measures, which is the topic of the current section.

Measures and calculated columns both use DAX expressions; the difference is the context of evaluation. A measure is evaluated in the context of the cell of the pivot table or DAX query, whereas a calculated column is evaluated at the row level of the table to which it belongs. The context of the cell (later in the book, you learn that this is a filter context) depends on the user selections on the pivot table or on the shape of the DAX query. When you use SUM([SalesAmount]) in a measure, you mean the sum of all the cells that are aggregated under this cell, whereas when you use [SalesAmount] in a calculated column, you mean the value of the SalesAmount column in this row.

When you create a measure, you can define a value that changes according to the filter that the user applies on a pivot table. In this way, you can solve the problem of calculating the gross margin percentage. To define a measure, you can click anywhere inside the measure grid and write the following measure formula by using the assignment operator :=:.

GrossMarginPct := SUM( FactInternetSales[Gross Margin] ) / SUM( FactInternetSales[SalesAmount] )

You can see the formula bar in Figure 4-5.

Figure 4-5 You can create measures in the formula bar.

After the measure is created, it is visible in the measure grid, as you can see in Figure 4-6.

Figure 4-6 Measures are shown in the measure grid.

A few interesting things about measures are shown in the measure grid. First, the value shown is dynamically computed and takes filters into account. Thus, the value 0.41149… is the gross margin in percentage for all AdventureWorks sales. If you apply a filter to some columns, the value will be updated accordingly.

You can move the measure anywhere in the measure grid by using the technique of cut and paste. To move the measure, cut it and paste it somewhere else. Copy and paste also works if you want to make a copy of a formula and reuse the code.

Measures have more properties that cannot be set in the formula. They must be set in the Properties window. In Figure 4-7, you can see the Properties window for the example measure.

Figure 4-7 Measures properties are set in the Properties window.

The Properties window is dynamically updated based on the format of the measure. In Figure 4-7, you can see that the default format for a measure is General. The General format does not have any formatting property. Because you want to format the measure as a percentage (0.41149 really means 41.15%), change the format to Percentage. The updated Properties window (see Figure 4-8) now shows the number of decimal places among the properties of the measure.

Figure 4-8 The properties of a measure are updated dynamically based on the format.

Editing Measures by Using DAX Editor

Simple measures can be easily authored by using the formula bar, but, as soon as the measures start to become more complex, using the formula bar is no longer a viable option. Unfortunately, SQL Server Data Tools (SSDT) does not have any advanced editor in its default configuration.

As luck would have it, a team of experts has developed DAX Editor, a Microsoft Visual Studio add-in that greatly helps in measure authoring. You can download the project from CodePlex at http://daxeditor.codeplex.com.

DAX Editor supports IntelliSense and automatic measure formatting and enables you to author all the measures in a project by using a single script view, which is convenient for developers. In addition, DAX Editor enables you to add comments to all your measures, resulting in a self-documented script that will make your life easier when maintaining the code.

In Figure 4-9, you can see the DAX Editor window with a couple of measures and some comments.

Figure 4-9 DAX Editor has syntax highlighting and many useful functions to author DAX code.

We do not want to provide here a detailed description of this add-in, which, being on CodePlex, will be changed and maintained by independent coders, but we strongly suggest that you download and install the add-in. Regardless of whether your measures are simple or complex, your authoring experience will be a much better one.

Choosing Between Calculated Columns and Measures

Now that you have seen the difference between calculated columns and measures, you might be wondering when to use calculated columns and when to use measures. Sometimes either is an option, but in most situations, your computation needs determine your choice.

You must define a calculated column whenever you intend to do the following:

• Place the calculated results in an Excel slicer or see results in rows or columns in a pivot table (as opposed to the Values area).

• Define an expression that is strictly bound to the current row. (For example, Price * Quantity must be computed before other aggregations take place.)

• Categorize text or numbers (for example, a range of values for a measure, a range of ages of customers, such as 0–18, 18–25, and so on).

However, you must define a measure whenever you intend to display resulting calculation values that reflect pivot table selections made by the user and see them in the Values area of pivot tables, for example:

• When you calculate profit percentage of a pivot table selection

• When you calculate ratios of a product compared to all products but filter by both year and region

Some calculations can be achieved by using calculated columns or measures, even if different DAX expressions must be used in these cases. For example, you can define GrossMargin as a calculated column.

= FactInternetSales[SalesAmount] - FactInternetSales[TotalProductCost]

It can also be defined as a measure.

= SUM( FactInternetSales[SalesAmount] ) - SUM( FactInternetSales[TotalProductCost] )

The final result is the same. We suggest you favor the measure in this case because it does not consume memory and disk space, but this is important only in large datasets. When the size of the database is not an issue, you can use the method with which you are more comfortable.