1. The CALCULATE function allows you to override or add to the current context before an expression is calculated
1.1. When using the measure branching technique (which the Udemy course strongly advocates, and I agree is a great way to break down complex calculations into manageable parts), we can substitute the word "expression" for "measure"
1.2. When considering a new measure, a good way to think about it is to identify what is the current context (in whatever visual you want to add your measure to) and how to you want to change that context in order to get a different result?
2. Experimenting with CALCULATE is a good way to improve your understanding of the function
2.1. Let's start by considering the scenario shown to the right. We want to add a new measure to the table visual. The current context involves a filter on date (from the slicer) and a filter on customer name.
2.1.1. Thinking about current context before adding a new measure to table visual
2.2. Let's imagine we want a calculation that returns [Total Sales] per customer but for all dates. In other words, we want to override the date filter in the current context.
2.2.1. Calculate Example 1 = CALCULATE( [Total Sales], ALL( 'Dates' ) )
2.2.1.1. Remember that ALL is a table function that returns a virtual table (in this case a virtual table that includes every date in the 'Dates' table) and it replaces (i.e. overrides) any filter added by current context that involves any column from the 'Dates' table (in this example)
2.2.1.2. We see bigger numbers are returned when adding measure to table visual
2.2.1.2.1. If we adjust the date slicer, we see that the numbers of new measure do not change whilst [Total Sales] does change
2.3. As another example, let's imagine we have a table visual of [Total Sales] by date and we want to add a measure that shows the [Total Sales] for the same date the previous year.
2.3.1. In this case, we don't want to use a table function to override the date context, but rather, a time intelligence function
2.3.1.1. Calculate Example 2 = CALCULATE( [Total Sales], SAMEPERIODLASTYEAR( 'Dates'[Date] ) )
2.3.1.1.1. When we add to table visual, we see values come through as long as there were sales for that date last year
3. Understanding the sequence of context transition
3.1. It is a general rule that nested DAX functions that are embedded inside the CALCULATE function are evaluated in current context ***before* ** the outer CALCULATE function alters the final context
3.1.1. Take the following example:
3.1.1.1. Calculcate Example 1 = CALCULATE( [Total Sales], FILTER( ALL( 'Dates' ), 'Dates'[Date] > MAX( 'Dates'[Date] ) - 7 && 'Dates'[Date] <= MAX( 'Dates'[Date] ) ) )
3.1.1.1.1. and put it in context
4. CALCULATE with USERELATIONSHIP
4.1. The most common scenario for USERELATIONSHIP is fact tables with multiple date columns
4.1.1. There can only be one active relationship between two tables but we can use USERELATIONSHIP to switch active relationships (in this example from Order Date to Ship Date)
4.1.1.1. Note the inactive relationship shown with dotted line
4.1.1.1.1. It is essential for the inactive relationship to be modelled in order for the USERELATIONSHIP function to work
4.1.2. Here is an example measure to demonstrate:
4.1.2.1. Total Sales by Ship Date = CALCULATE( [Total Sales], USERELATIONSHIP( 'Dates'[Date], 'Sales'[Ship Date] ) )
4.1.2.1.1. Note the different results compared to the [Total Sales] that uses the active relationship based on Order Date
5. A few simple CALCULATE examples
5.1. Top Product Total Sales, Total Profit for High Margin Customers and Cumulative Sales
5.1.1. Top Product Total Sales = CALCULATE( [Total Sales], FILTER( Products, Products[Product Group] = "Top") )
5.1.2. Total Profit for High Margin Customers = CALCULATE( [Total Profit], FILTER( Customers, [Profit Margin] > 0.32 ) )
5.1.3. Cumulative Sales = CALCULATE( [Total Sales], FILTER( ALLSELECTED( 'Dates' ), 'Dates'[Date] <= MAX( 'Dates'[Date] ) ) )