563 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
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.
As mentioned earlier, you create measures in SSAS Tabular using a language called DAX. This page gives some of the underlying syntax rules.
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:
|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.|
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:
|<>||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.
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.|
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.
|Parts of this blog|
25 Aytoun Street