cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
arunaramana
Helper III
Helper III

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

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:

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

 

 

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

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.

View solution in original post

1 REPLY 1
Icey
Community Support
Community Support

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.

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.