This page has 0 threads | Add post

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

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.

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

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!

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

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

Google Sheets

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