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
- 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:
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 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:
Now the DAX function is asking for a table and an expression, and not just a column.
This now gives the correct 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.
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:
A pivot table showing the average discount at species level.
And that's the end of this blog on basic DAX syntax!
- Measures, query context and tuples
- How to create measures - 7 steps to follow
- Syntax for writing measures
- Aggregating expressions - the X suffix functions (this blog)