# Microsoft SQL Server 2012 Analysis Services: DAX Basics

- 7/15/2012

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