context transition in power bi as defined by "Alberto Ferrarri" in his article

[arunaramana]

Hello There,

Recently I have gone through the article on "Context Transition" explained by "Alberto Ferrario /SQLBI". I have the following concerns which I am trying to understand. It has been a concern as I understood that context transition is something that is expected to deal with in DAX quite often. 

Would anyone help me understand. I shall be very grateful for your support and time.

Source of Information:

• https://www.sqlbi.com/articles/understanding-context-transition/

Concern 01:

The context transition in DAX is the transformation of row contexts into an equivalent filter context performed by CALCULATE and CALCULATETABLE

• equivalent in way, which is something I failed to interpret even after gone through the document source thoroughly?

Concern 02:

Product[SumOfUnitPrice] = SUM ( Product[Unit Price] )

Being a calculated column, it is computed in a row context.

Nevertheless, because SUM computes the sum of all unit prices visible in the current filter context, the result is, for each product, the sum unit prices from the entire table. In a calculated column there is no filter context, only a row context.

• Visible in the current filter context – how to understand this phrase?

Concern 03:

1. Product[SumOfSalesQuantity] = SUM ( Sales[Quantity] )

 2. Product[CalcSumOfSalesQuantity] = CALCULATE ( SUM ( Sales[Quantity] ) )

• The first calculated column returns the grand total of Sales[Quantity], because no filter context is active,
• whereas the one with CALCULATE returns the sum of Sales[Quantity] for the current product only,
• Because (of using calculate() function) the filter context containing the current product is automatically propagated to sales due to the relationship between the two tables:
  • What is filter context here?

Concern 04:

There is a big difference between the filter context introduced by context transition and the row context.

In fact, the filter context generated by CALCULATE places a filter on all the columns of the table to identify a single row, not on its row number. Thus, if you use context transition in a table that contains duplicated rows, the filter context generated by CALCULATE includes all the duplicates.

Thus, it is safe to rely on context transition to filter a single row if and only if the table does not contain any duplicated row. This, of course, holds true if the table has a primary key that guarantees the absence of duplication.

• When would we have such a scenario?

It is important to note that context transition happens before further filters in CALCULATE. Thus, filters in CALCULATE might override filters produced by context transition

Couldn’t find any reference to filter-context introduced by row-context in this paragraph. However, what does it mean by filter-context introduced by row-context?

Product[SumOfUnitPriceAllProductKey] =

CALCULATE (

    SUM ( Product[Unit Price] ),

    ALL ( Product[ProductKey] )

)

• ALL removes any filter from ProductKey and, because it is applied after context transition, it also removes the filter on ProductKey introduced by context transition

•  Didn’t understand by filters on ProductKey introduced by context transition

[san17680]

Hi,

sorry to barge in..

I came to this post while looking for the meaning of context transition.

Does this statment fit in the context i have described below.."There is a big difference between the filter context introduced by context transition and the row context".

just tring to understand context transition wrt below. the below does not have implicit calculate in the filter argument of calculate, only a table name.  Now assuming I have a country name in the BI visual from customer table, what sort of context transition will this trigger?

will it be just row context transitioning to filter context. All rows from customer table become columns due to filter context and filter propagation.

=CALCULATE(COUNTROWS(Sales),Customer)

Thanks

[Icey · Accepted solution]

Hi @arunaramana ,

First of all, it is suggested to study Row Context and Filter Context in DAX.

• Row Context means current row. Simply put, each row's value of each column will affect the result of the current calculated cell.
• Filter Context means a set of filters over the rows of the data model.

And then, understand the logic of CALCULATE function and CALCULATETABLE function.

• Note: CALCULATETABLE function performs exactly the same functionality, except it modifies the filter context applied to an expression that returns a table object.

Let's take CALCULATE function as an example. Below is its syntax.

CALCULATE(<expression>[, <filter1> [, <filter2> [, …]]])

Here are the parameters' definitions.

PARAMETERS

Term	Definition
expression	The expression to be evaluated.
filter1, filter2,…	(Optional) Boolean expressions or table expressions that defines filters, or filter modifier functions.

We can find that filters are optional.

• When filter expressions are provided, the CALCULATE function modifies the filter context to evaluate the expression. For each filter expression, there are two possible standard outcomes when the filter expression is not wrapped in the KEEPFILTERS function:

  • If the columns (or tables) aren't in the filter context, then new filters will be added to the filter context to evaluate the expression.
  • If the columns (or tables) are already in the filter context, the existing filters will be overwritten by the new filters to evaluate the CALCULATE expression.
• When we don't use filters in CALCULATE function, it achieves a specific requirement. It transitions row context to filter context. It's required when an expression (not a model measure) that summarizes model data needs to be evaluated in row context. This scenario can happen in a calculated column formula or when an expression in an iterator function is evaluated. Note that when a model measure is used in row context, context transition is automatic.

Let's clarify the above content before proceeding. Please tell me what you don't understand.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.