This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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. 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 This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.

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.

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

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

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:

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:

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!

Parts of this blog
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

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC