1. CALCULATE
1.1. This is the most important of all DAX functions
1.2. Creates a new filter context under which is calculates its first argument
1.3. It takes the outer filter context and merges it with the new filter context specified by the arguments that follow the first argument
1.3.1. When we write Boolean expressions for filter arguments in CALCULATE, these are actually converted behind the scenes to virtual tables
1.3.1.1. For example, the first example gets implicitly converted to the second example (both produce identical results)
1.3.1.1.1. Example 1:
1.3.1.1.2. Example 2:
1.3.2. When there is outer context applied to the same column or columns affected by the filter arguments of CALCULATE, that part of the outer context is ignored and replaced by the new CALCULATE context to form the merged context for the first argument to calculate in
1.3.2.1. For example, consider this measure and note how the outer context of Product 1, Product 2, Prodyct 3, etc. gets ignored and replaced by the new merged context produced by CALCULATE
1.3.2.1.1. Product 1 and 3 Sales = CALCULATE( [Total Sales], FILTER( ALL( 'Products' ), 'Products'[Product Name] IN {"Product 1", "Product 3"} ) )
1.4. In addition to accepting filter expressions to alter context, CALCULATE also accepts modifiers
1.4.1. KEEPFILTERS is known as a local modifier that keeps the outer filter context and then adds to that context
1.4.1.1. For example, we can see that this measure keeps the outer filter context and then adds an additional filter to the merged context
1.4.1.1.1. Product 2 & 4 Sales Only = CALCULATE( [Total Sales], KEEPFILTERS( 'Products'[Product Name] IN {"Product 2", "Product 4"} ) )
1.4.2. Other modifiers include ALL* (i.e. any function beginning with ALL), REMOVEFILTERS, CROSSFILTER, USERELATIONSHIP
1.5. CALCULATE also performs context transition when nested inside iterator functions
1.5.1. For example, consider this measure
1.5.1.1. Average Yearly Sales = AVERAGEX( VALUES( 'Dates'[Year] ), CALCULATE( SUMX( 'Sales', 'Sales'[Total Revenue] ) ) )
1.5.1.1.1. AVERAGEX creates a row context over all the distinct values of 'Dates'[Year] and CALCULATE converts this to a filter context and merges it with the outer filter context as part of context transition
1.5.2. Note also that context transition (from row to filter context) can happen even when CALCULATE is not explicitly present in the measure expression
1.5.2.1. This is because when you use measure branching (i.e. use one measure inside another), CALCULATE is automatically placed implicitly around that measure
1.6. It is important to understand the sequence in which all the steps of CALCULATE are performed
1.6.1. The following measure provides an example of CALCULATE with all possible steps present:
1.6.1.1. Average Yearly Sales by Ship Date = AVERAGEX( VALUES( 'Dates'[Year] ), CALCULATE( [Total Sales], 'Regions'[City] = "Auckland", KEEPFILTERS('Products'[Product Name] IN {"Product 1", "Product 3"}), USERELATIONSHIP('Sales'[Ship Date Converted], 'Dates'[Date]) ) )
1.6.1.1.1. The sequence is as follows:
1.6.1.1.2. Here we see the measure in report context
1.6.1.1.3. It is interesting to note that we can effectively reverse the effect of context transaction by adding one of the ALL* global modifiers as part of the CALCULATE filter arguments
1.7. Common CALCULATE patterns:
1.7.1. Some examples in report context
1.7.1.1. Previous Year (YoY)
1.7.1.1.1. Sales LY = CALCULATE( [Total Sales] , SAMEPERIODLASTYEAR( Dates[Date] ))
1.7.1.2. % YoY Change
1.7.1.2.1. % Change to LY = DIVIDE( [Total Sales] - [Sales LY] , [Total Sales] , 0 )
1.7.1.3. Year to Date (YTD)
1.7.1.3.1. Sales Year to Date = CALCULATE( [Total Sales] , DATESYTD( Dates[Date] ) )
1.7.1.4. YTD Previous Year
1.7.1.4.1. Sales Year to Date LY = CALCULATE( [Sales Year to Date], SAMEPERIODLASTYEAR( Dates[Date] ))
1.7.1.5. % YoY YTD Change
1.7.1.5.1. % Change to LYTD = DIVIDE( [Sales Year to Date] - [Sales Year to Date LY] , [Sales Year to Date] , 0 )
1.7.1.6. Cumulative
1.7.1.6.1. Cumulative Sales = CALCULATE( [Total Sales] , FILTER( ALL( Dates[Date] ), Dates[Date] <= MAX( Dates[Date]) ) )
1.7.1.7. Cumulative Previous Year
1.7.1.7.1. Cumulative Sales LY = CALCULATE( [Sales LY] , FILTER( ALLSELECTED( Dates ), Dates[Date] <= MAX( Dates[Date]) ) )
2. CALCULATETABLE and Variables
2.1. These are two separate topics but we can use an example of CALCULATETABLE to illustrate and explain variables
2.2. CALCULATETABLE works in the same way as CALCULATE, except where CALCULATE always returns a scalar value, CALCULATETABLE always returns a table
2.3. We typically use CALCULATETABLE inside other functions like CALCULATE as a way to achieve a filter with modified context
2.4. As an example, imagine we want a count of customers that have had sales in two particular cities: Invercargill and Nelson
2.4.1. Customers With Sales In Both Cities = CALCULATE( DISTINCTCOUNT('Customer'[Customer Index]), CALCULATETABLE( Sales, 'Regions'[City] = "Invercargill" ), CALCULATETABLE( Sales, 'Regions'[City] = "Nelson" ) )
2.4.1.1. Here we've used CALCULATETABLE in place of FILTER to capture filters for the two cities of interest
2.4.1.1.1. Note that we cannot simply replace CALCULATETABLE with FILTER in this use case because the filter argument that follows the table expression argument must reference a column from the table expression
2.4.1.1.2. What's happening here is as follows:
2.4.1.2. Now let's consider how we might modify this measure expression to use variables
2.4.1.2.1. There are two reasons for using variables. The most important reason is to break code down into smaller parts that make the code more readable. The secondary reason is performance.
2.4.1.2.2. Here is our first attempt, but we've gone too far (explanation after the code)
3. Virtual table examples featuring FILTER, CALCULATETABLE, TOPN and VALUES
3.1. We can use DAX functions that return virtual tables wherever a function accepts a "table" argument, and virtual tables are the key to unlocking advanced analytics
3.2. As an example, imagine we want a count of sales orders for only orders values that exceeded 20,000
3.2.1. Total Orders Above Level = VAR OrderValue = 20000 VAR AdjSalesTable = FILTER( Sales, Sales[Revenue] >= OrderValue ) RETURN COUNTROWS( AdjSalesTable )
3.2.1.1. FILTER iterates the 'Sales' table and returns a virtual table copy of 'Sales' based on the [Revenue] exceeding the threshold
3.3. As another example, imagine we want a count of sales orders that only pertain to our top 250 customers over the whole of time
3.3.1. Total Orders Top 250 Customers = VAR CustomerGroup = 250 VAR TopSalesGroup = CALCULATETABLE( Sales, TOPN( CustomerGroup, ALL( Customers ), CALCULATE( [Total Revenue], ALL( Dates ) ), DESC ) ) RETURN COUNTROWS( TopSalesGroup )
3.3.1.1. CALCULATETABLE enables us to change the filter context of the 'Sales' table, and hence produces a virtual table copy of 'Sales' based on the filter produced by TOPN
3.3.1.2. TOPN returns a virtual table based on 'Customers' where the [Total Revenue] across the whole of time falls within the 50 largest values
3.4. Here we see our examples visualised
3.5. Another example is based on top 5 customers where we want customer names as part of the outer filter context in the report visual
3.5.1. Top 5 Customers = CALCULATE( [Total Revenue], TOPN( 5, ALL( Customers ), CALCULATE( [Total Revenue], ALL( Dates ) ), DESC ), VALUES( Customers[Customer Names] ) )
3.5.1.1. Again we see TOPN used to alter the context of the [Total Revenue] calculation but the interesting difference is the use of VALUES as a second filter argument for CALCULATE. This is to bring back filter context for [Customer Names] , which we are using in our report visual
3.5.1.1.1. Here is the result
3.5.1.1.2. Here's what result would be like without VALUES
4. Iterating functions, including SUMX and how to use RELATED
4.1. These functions are recognisable by having an X on the end. They take a table argument that can either be a physical table or a virtual table produced by a table function. When evaluating, they iterate that table row by row and perform whatever calculation you pass as the second argument.
4.1.1. As an example, let's consider a model that includes a fact table called 'Sales' and a dimension table called 'Product'. 'Sales' holds [Quantity] and a key column relating it to 'Product' in a many-to-one relationship. 'Product' holds [Current Price].
4.1.1.1. To define a measure for [Total Sales], we're going to need SUMX
4.1.1.1.1. Total Sales = SUMX( Sales, Sales[Quantity] * RELATED( 'Product'[Current Price] ) )
4.1.1.2. Now imagine we want to compare [Total Sales] with only sales of multiple quantities where the product price is over 2000
4.1.1.2.1. Sales Price > $ 2000 & Quantity > 1 = VAR SalesQtyAbove1 = FILTER( Sales, Sales[Quantity] > 1 ) RETURN SUMX( SalesQtyAbove1, SWITCH( TRUE(), RELATED( 'Product'[Current Price] ) >= 2000, [Total Sales], 0 ) )