Formula Optimization Checklist

 Co-authored with ​@David Browne and ​@Julie Guérin 

The following points are tips* for writing formulas that execute as quickly as possible, by minimizing the necessary data volume and optimizing the operations.

The fundamental guidelines behind these tips are:  

• Sparsity should be maximized - equivalently, density should be minimized. More BLANKs = less data = easier computation. 
• the amount of data should be reduced (conditioned, filtered, removed) as much as possible before performing calculations. 

* Not absolute rules, and not an exhaustive list of formula-writing principles. 

◻ Do not use ISBLANK() or ISNOTBLANK().

Instead, use ISDEFINED().

✅ ISDEFINED() returns BLANK if the tested value is non-blank. 

❌ ISBLANK() returns FALSE if the tested value is non-blank. ❌
Therefore use ISDEFINED() to avoid densifying the metric. 

Note: Do not confuse ISBLANK() with IFBLANK(). 

IFBLANK should be used, but carefully. See: 

◻ Do not return FALSE or 0 from an IF().

Instead, leave the 3rd argument empty.

✅ IF(Condition, ReturnValue):  returns BLANK where the condition is false, favouring sparsity

❌ IF(Condition, ReturnValue, FALSE): returns FALSE where the condition is false, favouring density ❌
 

◻ Wrap equality checks in an IF(..., TRUE).

✅ IF(M1 = M2, True): returns BLANK where the equality check is false, favouring sparsity
❌ M1 = M2: returns FALSE where the equality check is false, favouring density ❌

◻ Guard heavy operations with sparse checks using IFDEFINED(). 

Prevent unnecessary full-dimension calculations by only doing the operation on non-blank data points. 
✅ IFDEFINED(MySparseMetric, MySparseMetric[ADD: MyDimension], BLANK)

Particularly important for computationally-heavy operations such as ADD, REMOVE, and CUMULATE.  

◻ Filter data as early as possible.

Aim to perform calculations on the minimum number of cells possible by reducing before computing. Begin every formula by scoping out relevant combinations (filters, ISDEFINED checks, etc.) before building the rest of the logic. 

◻ The order of modifiers matters.

Optimal modifier order: 

1. FILTER / SELECT (reduce data first)
2. BY - Aggregation
3. REMOVE
4. BY - Allocation
5. ADD (expand dimensions last)

➡ ️ At the filtering step, if a Dimension is not needed in the output, the SELECT is even better than FILTER because it will remove the unneeded Dimension (retaining only the selected items). 

➡ It is especially important to apply REMOVE before expensive operations such as CUMULATE. 

◻ Do not repeat pieces of a formula. 

Follow the principle: calculate once and re-use.

If the same calculation is repeated multiple times in a formula, then that piece of the formula should be extracted to its own metric where it will be computed a single time, then can be referenced and re-used as many times as needed.