DAX Calculations and Queries - SQL Server Analysis Services 2012 Cube Development - page 374

Creating measures
A calculated measure performs aggregation in the model efficiently by processing cal-
culations on the server during query time. Measures can be defined using simple ag-
gregate functions, or they can include complex business logic by using combinations
of functions.
Getting ready
In a Tabular Model, a table can play two roles either as a container for measures (of-
ten referred to as a measure group or fact table) or a lookup table (often referred to
as a dimension table in multidimensional models). When a model is processed, the
Tabular processing engine determines the role of tables and applies the appropriate
metadata to them in the final model. If a table has non-summarized or non-numeric
columns, it shows up in the client application as a lookup table and these columns be-
come attributes. If a table has numeric columns that can be summarized or it contains
defined measures, it may be presented by the client tool as a measure group. If a
table contains the attribute columns and summary numeric columns and/or measures,
it can actually play both the roles and will show up in most client tools as two separ-
ate tables having the same name. Remove or hide all of the columns and leave only
the measures in a table containing the measure base columns. Column and meas-
ure names must be unique in a table, so it's a good idea to plan ahead when naming
columns and make it a point to use different names for measures and the numeric
columns in your measure table. The following exercise will make this clear.
How to do it…
Creating measures can be done in two ways:
• Creating AutoSum measures
• Creating calculated measures
Creating AutoSum measures
Simple measures may be added to a model that aggregate a column of numeric val-
ues. The AutoSum feature creates a measure using a selected aggregate function
(such as SUM , AVERAGE , DISTINCTCOUNT , COUNT , MIN , or MAX ) and automatically