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.
The context transition in DAX is the transformation of row contexts into an equivalent filter context performed by CALCULATE and CALCULATETABLE
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.
2. Product[CalcSumOfSalesQuantity] = CALCULATE ( SUM ( Sales[Quantity] ) )
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.
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] )
)
Solved! Go to Solution.
Hi @arunaramana ,
First of all, it is suggested to study Row Context and Filter Context in DAX.
And then, understand the logic of CALCULATE function and CALCULATETABLE function.
Let's take CALCULATE function as an example. Below is its syntax.
CALCULATE(<expression>[, <filter1> [, <filter2> [, …]]])
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:
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.
Hi @arunaramana ,
First of all, it is suggested to study Row Context and Filter Context in DAX.
And then, understand the logic of CALCULATE function and CALCULATETABLE function.
Let's take CALCULATE function as an example. Below is its syntax.
CALCULATE(<expression>[, <filter1> [, <filter2> [, …]]])
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:
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.
User | Count |
---|---|
284 | |
164 | |
71 | |
62 | |
51 |
User | Count |
---|---|
265 | |
183 | |
84 | |
67 | |
62 |