Forum Discussion
Expanded table
In this measure, does the expanded table "Winesales" in the second calculate override the effect of context transition. Can someone explain how this works.
Also if i replace the second calculate with SumX(Winesales, [Total Sales])* 0.05 . Does it yield the same result.
The expanded table "Winesales" in the second CALCULATE does not overwrite the effect of the context transition. Instead, it ensures that the calculation of [Total Sales] is done over the entire Winesales table, ignoring any filters that might be applied due to the context transition.
Original DAX Expression
CALCULATE(
[No of Sales],
FILTER(
Winesales,
[Total Sales] > CALCULATE([Total Sales], Winesales) * 0.05
)
)
Explanation
Context Transition: When CALCULATE is used, it transforms the row context into a filter context. This means that any row context that exists is converted into a filter context, which can affect the calculation of measures.Table Expansion: When you reference a table inside a CALCULATE function, it can expand to include related tables, which can affect the results of the calculation.
In the expression CALCULATE([Total Sales], Winesales), the Winesales table is referenced explicitly. This ensures that the calculation of [Total Sales] is done over the entire Winesales table, ignoring any filters that might be applied due to the context transition. This is crucial for calculating the correct threshold value.
3 Replies
Hi Cyriackpazhe
The attached document explaining to do the calculation step by step by defining variables to make it simple. And this is standard practice. Sumx to be used if you need row by row operation whis is not required here.
My doubt is does the expanded "Winesales" In second calculate overwrite the effect context transition that happens
The expanded table "Winesales" in the second CALCULATE does not overwrite the effect of the context transition. Instead, it ensures that the calculation of [Total Sales] is done over the entire Winesales table, ignoring any filters that might be applied due to the context transition.
Original DAX Expression
CALCULATE(
[No of Sales],
FILTER(
Winesales,
[Total Sales] > CALCULATE([Total Sales], Winesales) * 0.05
)
)
Explanation
Context Transition: When CALCULATE is used, it transforms the row context into a filter context. This means that any row context that exists is converted into a filter context, which can affect the calculation of measures.Table Expansion: When you reference a table inside a CALCULATE function, it can expand to include related tables, which can affect the results of the calculation.
In the expression CALCULATE([Total Sales], Winesales), the Winesales table is referenced explicitly. This ensures that the calculation of [Total Sales] is done over the entire Winesales table, ignoring any filters that might be applied due to the context transition. This is crucial for calculating the correct threshold value.
