Microsoft Office Tutorials and References
In Depth Information
Despite its complexity, this calculation is pretty fast. The key point is to understand all the
side effects when a filter returns more columns than those you explicitly specified in the filter
condition itself, which is something you have to consider carefully each time you use one or
more FILTER functions in a CALCULATE expression.
understanding the EARLIER Function
You have seen that there are two distinct concepts in evaluation context: the row context and
the filter context. The CALCULATE function can change the evaluation context, and functions
such as FILTER, SUMX, and other aggregation functions ending with an X character define
a new row context for each iterated value. It is interesting to note that a new row context
might be generated while an external operation in the same expression was using another
row context. In other words, a row context might be nested within another one while you use
several DAX functions nested in each other. However, only the innermost row context is the
one that remains active; none of the others is considered anymore, by default.
For example, consider the table shown in Figure 6-37, which you can find in the companion
FIguRE 6-37 The table used to demonstrate the EARLIER behavior.
You can create a linked table in PowerPivot and then define a calculated column with the
year-to-date value updated for each row. This is just a theoretical example because we see
later in Chapter 7, “Date Calculations in DAX,” that there are specific functions to calculate
year-to-date values, and most of the time it is better to use a measure instead of a calculated
column to do this type of calculation. Nevertheless, trying to solve the year-to-date issue by
writing a DAX expression can be helpful for understanding the EARLIER function behavior.