BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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.
- Measures, query context and tuples
- How to create measures - 7 steps to follow
- Syntax for writing measures (this blog)
- 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:
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.
- Measures, query context and tuples
- How to create measures - 7 steps to follow
- Syntax for writing measures (this blog)
- Aggregating expressions - the X suffix functions