563 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
A must-read DAX primer on how to create measures (including query context)
Part two 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 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:
|'||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.
|Parts of this blog|
25 Aytoun Street