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.

  1. Measures, query context and tuples
  2. How to create measures - 7 steps to follow (this blog)
  3. Syntax for writing measures
  4. Aggregating expressions - the X suffix functions

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

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:

Average price

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:

Data model diagram

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:

Measure cell

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:

Average transaction price

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:

Measure with spaces

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:

Changing a measure 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):

Renaming a measure

You can overtype the measure name using this property. 


For this example, I'm going to call the measure Average price:

Average price measure

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:

Measure format property

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:

Average price 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:

Filtering by quantity

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:

Filtering by product

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 3 filtered rows

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:

Pivot table fields

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:

Average price correct

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.

This blog has 0 threads Add post