A must-read DAX primer on how to create measures (including query context)
Part three of a four-part series of blogs

To create formulae in SSAS Tabular you need to learn DAX, the language used for creating measures. This blog shows the basic syntax of DAX, and explains how SSAS Tabular uses query context in a pivot table to aggregate data correctly.

  1. Measures, query context and tuples
  2. How to create measures - 7 steps to follow
  3. Syntax for writing measures (this blog)
  4. Aggregating expressions - the X suffix functions

This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.

Posted by Andy Brown on 11 January 2016

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Syntax for writing measures

As mentioned earlier, you create measures in SSAS Tabular using a language called DAX.  This page gives some of the underlying syntax rules.

Data Types

Some languages are fussy about the types of data (think C#, SQL).  DAX isn't!  The main data types that you can use are the following:

Data type Notes
Integer Whole numbers (ie without decimal places). Integer data types use 64 bits, and can be between -2263-1 and +263-1 (ie to all intents and purposes as large or small as you want).
Real Numbers containing decimal places. DAX uses 64 bits for real numbers, and they can range from approximately -1.7 * 10308 to 2.2 * 10308 (ie to all intents and purposes infinite).  
Date A date/time in the usual Microsoft format (where the integer part of the date represents the number of days since 1st January 1900). 
Boolean Can be either TRUE or FALSE.
String A string with maximum length (assuming Unicode format) of 268,435,456 characters! 
Blank Equivalent to null in SQL.

Symbols that you can use

Most of the symbols that you can use in DAX formulae are the same in all Microsoft applications. Here are a few which may catch you out: 

Symbol Use to
<> Check if one value does not equal another.
& Concatenate two items of text together.
&& Check if two things are both true (other packages use and instead).
|| Check if either of two conditions is true (other packages use or instead).
| Negation operator (checks if something is not true).

Note that you can actually also use the Excel-style AND, OR and NOT functions in DAX to test combinations of conditions.

Aggregation functions

DAX includes the following main functions to aggregate a column (the ones shown with an asterisk work for numbers and dates only):

Function What it does
AVERAGE (*) Returns the average of the numbers in a column.
COUNT  Returns the number of values in a column.
COUNTA Returns the number of non-empty values in a column.
COUNTBLANK Returns the number of blank values in a column.
DISTINCTCOUNT Returns the number of different values in a column.
MAX (*) Returns the largest value in a column.
MIN (*) Returns the smallest value in a column.
STDEV.P (*) Returns the standard deviation of the entire population for a column.
STDEV.S (*) Returns the sample standard deviation for a column. 
SUM (*) Adds the numbers in a column.
VAR.P (*) Returns the variance of the entire population for a column. 
VAR.S (*) Returns the sample population variance for a column. 

An example of an aggregation function in use (DISTINCTCOUNT)

Here's an example of a measure to count the distinct number of quantities bought for purchases:

Distinct count example

This measure will give the number of different quantities bought. Even though there are 13,904 transactions, all of these were for either 1, 2, 3 or 4 items at a time, so the measure returns the count 4.

 

The DISTINCTCOUNT function surprises pleasantly on all fronts: it’s new (having been introduced in SQL Server 2012), and it’s fast (much faster than its multi-dimensional model equivalent).

 

The next part of this blog looks a a special category of functions ending with the letter X, which allow you to aggregate an expression, and not just a column.

  1. Measures, query context and tuples
  2. How to create measures - 7 steps to follow
  3. Syntax for writing measures (this blog)
  4. Aggregating expressions - the X suffix functions
This blog has 0 threads Add post