1. Row context versus filter context
1.1. Calculated columns start off in row context, but there are ways to transition that to filter context
1.1.1. Row context has no awareness of table relationships
1.1.1.1. To demonstrate, consider adding [Customer Sales] as a calculated column to the 'Customers' table
1.1.1.1.1. Customer Sales = SUM( 'Sales'[Total Revenue] )
1.1.1.1.2. We can transition to filter context by wrapping the SUM function in the CALCULATE function
1.1.1.1.3. When we specify a measure for the calculated column, this also causes implicit context transition from row to filter context (i.e. same as if we used CALCULATE)
1.2. We can explore the nuances of context transition and potential pitfalls via an iterating function like SUMX
1.2.1. Let's start by comparing these two measures in a table visual that includes date
1.2.1.1. Total Sales = SUM( Sales[Total Revenue] )
1.2.1.2. Example Iter. Sum 1 = SUMX( Sales, 'Sales'[Total Revenue] )
1.2.1.3. We see they both produce the same results
1.2.2. Next we try adding another variation, replacing the column expression with the [Total Sales] measure
1.2.2.1. Example Iter. Sum 2 = SUMX( Sales, [Total Sales] )
1.2.2.2. Again, the same results
1.2.3. But if we try replacing the [Total Sales] with the SUM expression of that measure, we see that the context transition causes incorrect results
1.2.3.1. Example Iter. Sum 3 = SUMX( Sales, SUM( 'Sales'[Total Revenue] ) )
1.2.3.2. Now the results have gone haywire
1.3. The main way to avoid the pitfalls of context transition is to stick to the principles of branching (defining measures that use other measures, that use other measures, etc.), thereby avoiding complex expressions within individual DAX formulas
1.3.1. We could resolve the example #3 given by wrapping the inner SUM in CALCULATE but this is unnecessarily complicated because if you stick to using measures for the expression argument, the context should always work out
2. Effect of table relationships on context and formula results
2.1. Imagine we have a table visual with 'Customers'[Customer Name] and we want to add a measure that counts how many regions each customer purchased from
2.1.1. We can try two different approaches, both using COUNTROWS
2.1.1.1. Test Count Sales Regions = COUNTROWS( VALUES( 'Sales'[Delivery Region Index] ) )
2.1.1.2. Test Count Regions = COUNTROWS( VALUES( 'Regions'[Index] ) )
2.1.2. If we add these two measures to our table visual, we see different results
2.1.2.1. Test Count Regions is just repeating the total number of 'Region' records over and over
2.1.3. The reason for this behaviour is due to our model. When our relationships between dimension tables and fact tables are one way (dimension to fact), which is considered best practice, this means the filter on region cannot flow up from 'Sales' to 'Region' because it goes against the grain.
2.1.3.1. 1-to-many single direction relationships
2.1.3.1.1. Although we could resolve this by making the relationship between 'Regions' and 'Sales' bi-directional, this is considered bad practice as it can lead to filter path ambiguity, and inconsistent (and unpredictable) results.
3. ISFILTERED vs ISCROSSFILTERED
3.1. Both of these functions return True or False and are typically used inside an IF function
3.2. Example #1
3.2.1. IsFiltered example 1 = ISFILTERED( 'Customers' )
3.2.1.1. We see this returns True for each row because 'Customers'[Customer Names] is included in the table visual
3.3. Example #2
3.3.1. IsFiltered example 2 = ISFILTERED( 'Sales'[Customer Name Index] )
3.3.1.1. We see this returns False because the [Customer Name Index] column in the 'Sales' table is not explicitly filtered in this table visual
3.4. Example #3
3.4.1. IsCrossFiltered example 1 = ISCROSSFILTERED( 'Sales'[Customer Name Index] )
3.4.1.1. In contrast to ISFILTERED, we see this returns True because this column is ***implicitly*** filtered
3.5. Example #4
3.5.1. IsCrossFiltered example 2 = ISCROSSFILTERED( 'Products' )
3.5.1.1. We see this returns False because none of the filters in play touch the 'Products' table
3.5.1.1.1. As a way of confirming this, compare the visual's filters with the model
3.6. Finally, here's an example of how we might use one of these functions as part of a measure
3.6.1. % of Total Customer Sales = IF( ISCROSSFILTERED( 'Customers' ), DIVIDE( [Total Sales], [All Customer Sales], 0 ), BLANK() )
3.6.1.1. In this example, we only want results for this measure when there is some filter applying on the 'Customers' table
4. RELATED
4.1. We use this function inside measure formulas to retrieve a value from another related table
4.1.1. A good example of this is the 'Sales' fact table holding an [Order Quantity] metric and the 'Products' dimension table holding the [Unit Price] metric. We want to create a [Sales Amount] measure that multiples these two metrics together.
4.1.1.1. If we look at our model, we can note that the RELATED function will allow us to traverse against the grain of the relationship direction (from 'Sales' to 'Products')
4.1.1.2. Here's an example measure:
4.1.1.2.1. Sales Amount = SUMX( 'Sales', 'Sales'[Order Quantity] * RELATED( 'Products'[Unit Price] ) )
5. EARLIER
5.1. This function is mostly used in the context of calculated columns. It is used within nested calculations to break out of a layer (by default, 1 layer) of context in order to retrieve some number.
5.2. The course instructor says this function existed before variables were introduced to DAX and that it is better to use variables than EARLIER (i.e. he thinks using variables is more intuitive and has made the EARLIER function redundant).
5.3. Example: add a calculated column named "Most Expensive" to a 'Products' table
5.3.1. Most Expensive = COUNTROWS( FILTER(Products, EARLIER(Products[Unit Price]) < Products[Unit Price] ) ) + 1
5.3.1.1. A calculated column starts in a row context and FILTER creates another row context, in this case across the whole of the Products table.
5.3.1.2. The EARLIER function breaks out of that second layer of row context created by FILTER, enabling us to implement a ranking function.
5.3.1.3. We can see the result is to rank each product from most expensive to least expensive
5.3.1.3.1. Note the correlation of [Unit Price] and [Most Expensive]
5.3.1.4. We can achieve exactly the same result by using variables. The instructor says this works because variables are always assessed before the rest of the formula (i.e. before the added contet layer created by the FILTER function in this case).
5.3.1.4.1. Most Expensive = VAR UnitPrice = 'Products'[Unit Price] RETURN COUNTROWS( FILTER(Products, UnitPrice < Products[Unit Price] ) ) + 1