1. Using variables as alternative to measure branching
1.1. Only do this when you have an intermediary measure that you are confident will not be used in any other measures
1.2. As an example, imagine that we want a measure for export sales by ship date. We could create the following measure as an intermediary measure...
1.2.1. Total Sales by Ship Date = CALCULATE( [Total Sales], USERELATIONSHIP( 'Dates'[Date], 'Sales'[Ship Date] ) )
1.2.1.1. But we decide that [Total Sales by Ship Date] is only needed for our ultimate measure, [Export Sales by Ship Date], so we ditch the intermediary measure and put it in a variable as follows:
1.2.1.1.1. Export Sales by Ship Date = VAR SalesByShipDate = CALCULATE( [Total Sales], USERELATIONSHIP( 'Dates'[Date], 'Sales'[Ship Date] ) ) RETURN CALCULATE( SalesByShipDate, FILTER( 'Channels', 'Channels'[Channel] = "Export" ) )
2. Example without using any variables
2.1. Sales of Top Products = CALCULATE( [Total Sales], FILTER( Products, [Total Sales] > 100000 ) )
2.1.1. Note the context when adding this measure to a table visual
2.1.1.1. By focusing in on the highlighted values, let's remember what this calculation is doing
2.1.1.1.1. Firstly, by wrapping [Total Sales] in CALCULATE, we are announcing our intention to alter the evaluation context of Q3 2020 and customer 21st Ltd
2.1.1.1.2. The FILTER function generates a virtual table of all products that have [Total Sales] greater than 100,000
2.1.1.1.3. The FILTER function ***subtracts*** from the existing evaluation context, so our calculation is based on Q3 2020, customer 21st Ltd and then only products for which [Total Sales] exceeds 100,000 within the same context
3. Example with variables (for same result)
3.1. Sales of Top Products = VAR ProductThreshold = 100000 VAR TopProducts = FILTER( Products, [Total Sales] > ProductThreshold ) RETURN CALCULATE( [Total Sales], TopProducts )
3.1.1. The result is identical to the above
3.1.1.1. The variables are evaluated first and are immutable in the context of each calculation (i.e. we cannot use a variable in a loop, changing its value on each loop iteration)
4. Another example without variables
4.1. Sales of Top Products = CALCULATE( [Total Sales], FILTER( Products, CALCULATE( [Total Sales], ALL( 'Dates' ) ) > 100000 ) )
4.1.1. Here, we decide that we don't want the "Top Products" filter to be constrained by our date slicer selection, so we wrap our inner [Total Sales] inside a nested CALCULATE that uses ALL( 'Dates' ) to calculate Top Products across the whole of time
4.1.1.1. Let's look at this in context
4.1.1.1.1. We see here that [Sales of Top Products] returns a different number for Q3 2020 and customer 3LAB, Ltd because the context reduction implemented by FILTER has happened across the whole of time
5. Another example with variables (where simply moving sub-expressions to variables can produce different results)
5.1. This is an important point about variables being evaluated once and not always being able to substitute parts of our formulas with variables
5.1.1. Sales of Top Products = VAR TopProducts = CALCULATE( [Total Sales], ALL( 'Dates' ) ) RETURN CALCULATE( [Total Sales], FILTER( 'Products', TopProducts > 100000 ) )
5.1.1.1. Here we try to move the nested CALCULATE into a variable but it won't work in the same way as our non-variable original above.
5.1.1.1.1. Let's look in context and compare with the previous
6. Simplifying measures with variables
6.1. One of the main benefits of using variables in measures is to make the code more readable and understandable
6.2. Here's an example measure, without variables, where we want to capture high margin sales quantities for the Export channel
6.2.1. High Margin Export Quantities Sold = CALCULATE( [Total Sales Quantity], FILTER( 'Products', [Profit Margin] > 0.33 ), FILTER( 'Channels', 'Channels'[Channel] = "Export" ) )
6.2.1.1. We can move the filter expressions to variables and add comments
6.2.1.1.1. High Margin Export Quantities Sold = VAR HighMarginProducts = FILTER( 'Products', [Profit Margin] > 0.33 ) // This is our definition for high margin products VAR ExportChannel = FILTER( 'Channels', 'Channels'[Channel] = "Export" ) // Export channel only RETURN CALCULATE( [Total Sales Quantity], HighMarginProducts, ExportChannel )