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 (this blog)
- How to create measures - 7 steps to follow
- 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.
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:
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 versus Measures
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.
Query context and Tuples
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:
|Centre type||Retail Park|
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!