DAX – Filter Context V.S Row Context

A key to understanding DAX is to distinguish Filter Context and Row Context.

Here are definitions of each context.

Filter Context: can be simply defines as filter context the set of filters applied to the evaluation of a DAX expression, usually a measure, regardless of how they have been generated. Usually, every cell of a report has a different filter context, which can be defined implicitly by the user interface (such as the pivot table in Excel), or explicitly by some DAX expression using CALCULATE or CALCULATETABLE. Filter Context always exist before a DAX expression been evaluated.

Row Context: the concept of row context is always exist in a DAX expression. In order to get the value of a DAX expression, you need a way to tell DAX the row to use AKA checking the current row of a table and provide the value back. You have a row context whenever you iterate a table, either explicitly (using an iterator) or implicitly (in a calculated column).

Use one DAX measure as an Example:

SumofProductSalesAmount:=SUMX(

VALUES([Product].[ProductName]),   

CALCULATE (

        [Sales Amount]

    )

)

Explain in detail of this calculation:

1. VALUES Function returns a table, meaning it provide All the Distinctive ProductName back as a table outcome
2. SUMX iterate each row of the table in its first argument, then it aggregate the value each row returns in sum. In this case, for each row of a table which provided by VALUES function AKA for each product, it calculate the value in the second argument of the SUMX function. Then, Sum all the value together and return back to the caller.
3. Things happens outside CALCULATE Function are all treated as Filter Context of the CALCULATE expression. In this case, there are two places feeding the CACULATE section with Filter Context.
   • First place is, the Current Row defined by SUMX, in our case, it is the Current Row of VALUES([Product].[ProductName]) which will be a specific Product depends on where the iterator at. Meaning Evaluated [Sales Amount] Measure with a filter which limit to a certain Product, and then provide the value (sales amount) back.
   • Second Place is, whenever this measure [SumofProductSalesAmount] exposed in a cell user application (like Excel, PowerBI, SSRS), it exposed in a filter context set on that specific Cell. Depends on wherever your cell is at you have different filter context. For example you may have a filter on a cell to limit the time frame to 2017, etc.

Thanks,

Your friend – Annie.