This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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

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.

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

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 -2²⁶³-1 and +2⁶³-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 * 10³⁰⁸ to 2.2 * 10³⁰⁸ (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.

Parts of this blog
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

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR