Almost 3 years ago I wrote an article with the rules for DAX code formatting. If you quickly look at the article, you might think that it is all about readability of the code, and this is fundamentally true. But there two rules that have a particular importance for performance, too:
- Never use table names for measures
- Always use table names for column reference
- Even when you define a calculated column within a table
Well, it is not that writing/omitting table name has a direct impact on performance, but you can easily miss an important bottleneck in your formula. Let me clarify with an example. If I read this:
= [A] + SUMX ( Fact, Fact[SalesAmount] )
I would say that SalesAmount is a column of the Fact table, and the SUMX iteration will not perform a context transition. But if I read this:
= [A] + SUMX ( Fact, [SalesAmount] )
I would start to be worried about the number of rows in Fact table, because each one will invoke a context transition for the measure SalesAmount evaluated for each row of the Fact table, creating a different filter context for each evaluation.
This simple detail makes a huge difference in performance. Context transition is fast, but doing it million times require time.
Wait a minute: are you asking yourself what is a context transition and why a measure generate it? No problem: read the article Understanding Context Transition to get a quick recap of the question (and if you want to dig deeper, preorder The Definitive Guide to DAX, available in October 2015!
Returns the sum of an expression evaluated for each row in a table.
SUMX ( <Table>, <Expression> )