Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
581 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers 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 ...
Written by Andy Brown
In this tutorial
This blog explains what measures are, and how to create them.
This is the must-read blog in this entire SSAS Tabular tutorial. You'll need a thorough understanding of what measures are and how they work to create DAX formulae, and you should be confident you understand the concept of query context explained below before proceeding further.
A previous blog showed how to create data models. I'm going to assume that you've followed this, and have created the following base model:
For this blog, I've imported all of the rows in all of the tables shown (there are no filters applied).
Using this, you should be able to create a similar pivot table to the one below:
I've also created a slicer, and am using this to show data just for the Retail Park centre type.
What I want to do is to look at the shaded figure 595, representing the total sales for Bird products in the North region in Retail Park centres.
Calculated columns evaluate a formula for every single row in a table:
SSAS Tabular works out the value for each row for a calculated column - there is no aggregation involved.
By contrast, a measure always involves aggregation:
A measure ALWAYS involves aggregation (whether this be taking the total, maximum, minimum or count of the underlying values for the pivot table cell in question).
You put calculated columns and measures in different parts of a pivot table:
Calculated columns can only be filters, slicers, row or column headers; measures can only go in the VALUES section of a pivot table.
In this respect SSAS is less forgiving than its little sister PowerPivot, which allows you to put measures anywhere.
The thing to remember - always - is that any measure is evaluated for the current query context:
The figure 595 is obtained by summing the quantity sold for this pivot table cell's query context (see below).
The query context for the shaded cell 595 is as follows:
Dimension | Value |
---|---|
Centre type | Retail Park |
Species | Bird |
Quadrant | North |
You can express this as a tuple:
The word "tuple" is borrowed from SSAS Multi-dimensional, but is a useful way to show the query context for any calculation.
Let's take another example cell:
The tuple for the above figure 430 is thus:
You should be 100% certain you understand this concept before continuing with any part of this tutorial!
Having now got the measure of measures (that's a poor pun), let's look at how to create them!
This blog page shows (under separate headings) the 7 steps to follow to create a measure showing the average quantity sold:
The average price of Amphibian products sold in the East region in Retail Park centres was 7.76.
The best place to put a measure is at the level of granularity at which it applies. I'll see if I can now translate this into English. Consider the tables for our model:
The transaction table is at the child end of all of the relationships.
It's probably reasonably obvious that if you're going to calculate the average price across all of the transactions for any pivot table cell's query context, it makes sense to put the measure in the Transaction table.
The next question is in which cell to put your measure's formula:
The area at the bottom of a table (underneath all of the rows) contains any measures that you type in.
The surprising - to me, at any rate - answer is that ANY cell will do! In the diagram above I've selected one more or less at random (although choosing one underneath the Price column may have made more sense, it's not necessary).
I think this is a failing in the SSAS Tabular interface. There is no reason to store the measures you create in a grid at the bottom of each table (a separate storage space on screen would have been much more sensible).
You can now start typing in your measure:
Type in your formula, using the punctuation characters below.
You should find the following table of characters that you can use useful:
Key | Notes |
---|---|
' | The apostrophe key brings up a list of tables. |
[ | The opening square bracket key brings up a list of the columns in a table. |
SHIFT + ENTER | This key combination inserts a new line in a formula. |
Space bar | The space bar inserts blank spaces to make measures easier to read. |
Here's an example of how you can space out your measure formula:
Here I've used spaces and a carriage return to make the measure easier to read. You can click and drag on the line below the formula bar to make it bigger or smaller.
Note that for most tables you don't need to encase the name in apostrophes. Transaction is, sadly, an exception (being a special word in SQ)L, which is why we need to write it as 'Transaction'.
When you create a measure, SSAS Tabular automatically gives it a name:
You can change the measure name by overtyping it.
Another way to rename a measure is to press the F4 key if necessary to bring up its properties (note that if you've included spaces at the start of the measure, this property window may not be available):
You can overtype the measure name using this property.
For this example, I'm going to call the measure Average price:
The name of the measure has been changed.
If you include your measure in a pivot table or Power View report, it will inherit its formatting from your model, so it makes sense to set this now:
Press F4 if necessary to bring up the properties for the measure, and change the default format as shown here. You can also specify whether you want to display a thousands separator.
SSAS Tabular displays the calculated value for a measure based on the rows of a table currently being displayed:
Initially there are 13,904 transactions, the average price of which is 7.94 pounds.
You can test this by applying a filter to your data:
Here we've clicked on the drop arrow next to the Quantity column to show only those transactions where 4 items were bought simultaneously.
You can combine filters:
We're now using the same method to show only items for product number 4.
The result of these shenanigans is that we can only see 3 rows, but the average price is correct:
The average of 10.58, 11.27 and 8.625 is indeed 10.16.
You could now create a pivot table to show off your shiny new measure:
Here I've dragged the measure created into the VALUES section of the pivot table field list (I've also removed the filters set above).
Excel should show the average price correctly calculated for each cell:
The average price is calculated for each query context.
Note that the formatting is set correctly too.
Now that you know how to create a measure, let's look at the basic DAX syntax rules.
As mentioned earlier, you create measures in SSAS Tabular using a language called DAX. This page gives some of the underlying syntax rules.
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 -2263-1 and +263-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 * 10308 to 2.2 * 10308 (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. |
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.
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. |
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.
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!
You can learn more about this topic on the following Wise Owl courses:
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 2024. All Rights Reserved.