559 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
Search our website
We also send out useful tips in a monthly email newsletter ...
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.
This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources. |
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.
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!
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.
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. |
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 |
---|
|
Some other pages relevant to the above blogs include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2023. All Rights Reserved.