COVID-19: Choose between our familiar (but now socially distanced) classroom training courses and our excellent new live online courses.
A must-read DAX primer on how to create measures (including query context)
Part one 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 (this blog)
  2. How to create measures - 7 steps to follow
  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.

Measures, query context and tuples

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.

Our example model

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:

The 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:

Pivot table

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 versus Measures

Calculated columns evaluate a formula for every single row in a table:

A calculated column

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:

Measures require 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:

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.

Query context and Tuples

The thing to remember - always - is that any measure is evaluated for the current query context:

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:

{ Retail Park, Bird, North }

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:

Total query context

The tuple for the above figure 430 is thus:

{ Retail Park, ALL, East }

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 has 0 threads Add post