topic Re: Expanded table in DAX Commands and Tips

Expanded table

Cyriackpazhe — Tue, 18 Feb 2025 11:11:30 GMT

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.

Re: Expanded table

Rupak_bi — Tue, 18 Feb 2025 12:03:15 GMT

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.

Re: Expanded table

Cyriackpazhe — Tue, 18 Feb 2025 12:15:12 GMT

My doubt is does the expanded "Winesales" In second calculate overwrite the effect context transition that happens

Re: Expanded table

bhanu_gautam — Tue, 18 Feb 2025 12:53:27 GMT

Cyriackpazhe

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.