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 (this blog)
- Syntax for writing measures
- Aggregating expressions - the X suffix functions
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.
How to create measures - 7 steps to follow
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.
Step 1 - choosing where to put your measure
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.
Step 2 - choosing a cell for your measure
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).
Step 3 - Creating your formula
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:
|'||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'.
Step 4 - Naming your measure
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.
Step 5 - changing the default formatting
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.
Step 6 - applying filters to test your measure
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.
Step 7 - testing your measure in Excel
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.