A must-read DAX primer on how to create measures (including query context)
Part four 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
  4. Aggregating expressions - the X suffix functions (this blog)

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.

Aggregating expressions - the X suffix functions

It can be a bit of a shock in DAX to find that summing an expression using SUM doesn't work!  This blog page shows what the problem is, then shows the solution.

The problem: normal aggregation functions can only sum columns

Aggregation functions like SUM can only take columns as arguments:

Summing giving error

You can't sum an expression!  The full error message tells you that the SUM function only accepts a column reference as an argument.

On reflection, you can't say you weren't warned:

The SUM syntax

The function can only take a column name as an argument.

 

A golden rule of DAX: only give a function what it asks for!

The solution: X-suffix functions

There is a whole category of functions in DAX which aggregate expressions - you can work out what they are by sticking an X onto the end of a normal aggregation function name:

SUMX function

Now the DAX function is asking for a table and an expression, and not just a column.

 

This now gives the correct results:

SUMX function results

This formula sums the price multiplied by the quantity for the transactions table.

 

You could have solved this problem by creating a calculated column to give price multiplied by quantity, and using a simple SUM function to sum this.  You can see thoughts on which approach is "better" here.

The X-suffix functions available

Here's a list of the functions that you can use (a "table", as always in DAX, means any set of rows returned from a filter):

Function What it does
AVERAGEX Returns the average of the given expression evaluated over the rows of the specified table.
COUNT  Returns the number of values for the specified expression counted over the rows of the given table.
COUNTA Returns the number of non-blank values for the specified expression counted over the rows of the given table.
COUNTBLANK Returns the number of blank values for a given expression in a given table.
DISTINCTCOUNT Returns the number of different values for a given expression in a given table.
MAXX Returns the largest value of the given expression over the rows of the specified table.
MINX Returns the smallest value of the given expression over the rows of the specified table.
STDEVX.P Returns the standard deviation of the entire population for the given expression, taken over the rows of the specified table.
STDEVX.S Returns the sample standard deviation for the given expression, taken over the rows of the specified table.
SUMX Returns the sum of the given expression over the rows of the specified table.
VARP.X Returns the variance of the entire population for a given expression over the rows of a given table.
VAR.X Returns the sample variance for a given expression over the rows of a given table.

An example: using AVERAGEX to calculate the average discount

The following function would work out the average of the ratio of the price of each transaction to the list price for the relevant product. 

Average of discount

I've additionally imported the FullPrice column into the Product table to get this measure to work.

From this it appears that mammals attract the greatest discount:

Pivot table

A pivot table showing the average discount at species level.

 

 

And that's the end of this blog on basic DAX syntax!

This blog has 0 threads Add post