DAX Evaluation Context

DAX is a collection of functions, operators, and constants that can be used in a formula, or expression, to calculate and return one or more values.

A key to understanding DAX is understanding how evaluation context works. There are 2 major types of context: row context and filter context.

Row context can best be explained through an example. Consider the DAX formula for a calculated column:

[Units Sold] and [Sales Price] are column references, which intuitively mean that I want to retrieve the value of Units Sold and Sales Price. But to retrieve the value of any column, I need to specify which row to use.

I can do this implicitly using a calculated column, since we have created a calculated column, the current row of the table becomes the row context.

Let’s see what happens if I create a measure, instead of calculated column, using the exact DAX formula above.

Now this measure works, as SUMX iterates over ‘FactTable’ and for each row calculates the expression, thus introducing a row context.

Filter context is the set of filters applied to evaluation of DAX expression.

Let’s consider the following measure to calculate total sales

Showing Sales Measure1 against Country shows a different value for each country. How this happens is, each row in the table shown above introduces a filter context, for which DAX expression is evaluated.

To understand this concept further, let’s add some slicers to the report and see how it interacts with our measure.

You will see, the value of measure changes with each selection. How this happens is, the filter context is modified. Now the expression is calculated for the following set of filters

Year = 2014
Discount Band = Medium
Country = Canada, France, Germany, Mexico, United States of America

Let’s select 2014 in year slicer and add Sales Measure1 and Sales Measure2 against Discount Band in a table. What you expect to see is Sales Measure 1 showing values for all Discount Bands and Sales Measure2 showing value only for Discount Band Medium. But the result is rather unexpected.

Instead of showing value for Medium, Sales Measure2 repeats the same value for all bands. If you notice, this is the value of Medium which is repeated through out the column.

What happens here is, when an explicit filter context is defined for a column, it over rides any implicit filters that are applied to that specific column. Since, Sales Measure2 has Discount Band = Medium defined, no other filter on this column is applied, thus repeating the value of medium for all rows.

If I want to change this behavior and keep implicit filters as well, I can modify the DAX expression as follows:

Related posts:

A medical specialty with both medical and surgical aspects, dermatology deals with diseases of the skin, hair and nails. A dermatologist as such treats all diseases, including acute and cosmetic, of…

Any Business needs to have customers. And to get customers, you first have to make them aware of what you are offering. If you need to persuade someone to take action, you’re doing marketing. If…

Looking for the best golf simulator projector? We think we found it! In this video review we unbox, install, and review the BenQ LK953ST, a Short Throw 4K 5000 lumen laser projector in the GSV golf…