562 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 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.
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.
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 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.
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!
|Parts of this blog|
25 Aytoun Street