Writing Performant Formulas

Writing Performant Pigment Formulas

Apply this skill to every formula before delivery. Pigment is a sparse multidimensional engine: only defined cells are stored. Performance depends on keeping formulas sparse and scoped.

Simple same-dimension arithmetic (e.g. 'A' + 'B') needs no special performance wrapping. Always review the checklist; deep rewrite when any item fails. For profiler-based troubleshooting after delivery, see skill:diagnosing-performance-issues.

Pre-Delivery Checklist

Before delivering any formula, verify each item. Rewrite until all pass.

1. Identifiers: Correctly quoted — single quotes for names ('Revenue'), double quotes for items and text literals.
2. Dimension alignment: No unintended ADD or dimension mismatch.
3. Sparsity: No unnecessary 0, FALSE, or TRUE where BLANK suffices.
4. Existence checks: Prefer ISDEFINED / IFDEFINED / IFBLANK over ISBLANK / ISNOTBLANK (see skill:using-formula-functions for rare valid exceptions).
5. Scope-first: FILTER, EXCLUDE, or IFDEFINED appear before calculations, not after.
6. Aggregations last: REMOVE, BY, and other aggregations come after the core calculation.
7. Prior periods: Use SELECT with offset — not PREVIOUS unless true iteration is required (see skill:iterating-with-previous-and-cycles).
8. Allocation: Use BY with a mapping when one exists — not ADD.
9. Date ranges: Use PRORATA — not IF(Date >= Start AND Date <= End, ...).
10. BY guards: No IF / ISBLANK wrappers on BY when the source is dimension-typed.
11. Negation: Prefer EXCLUDE: condition over FILTER: NOT(condition).
12. Access rights: Wrap in IFDEFINED('Users roles', ...). Optionally add IFDEFINED(User, ...) as an additional performance trim.
13. Conditional output: Use IF(condition, value) (sparse) — not IF(condition, value, 0) or IF(condition, TRUE, FALSE).
14. Subsetting: Use FILTER: CurrentValue — not IF(expr, expr, BLANK).

Treat BLANK as Absence, Not Zero or False

Example: 1000 Products x 12 Months = 12,000 possible cells. If only 500 have values, sparse metric stores 500 cells (~4%). Densifying to 12,000 multiplies storage and computation.

Rule: Represent "no value" with BLANK. Never substitute 0 for empty numbers or FALSE for empty booleans unless downstream logic explicitly requires stored values.

Meaningful zero exception: Explicit 0 IS correct when zero is a business value (zero variance, zero balance, zero growth rate, inactive line item contributing 0 to a total). In these cases BLANK would incorrectly omit the line from aggregations.

Prefer ISDEFINED Over ISBLANK

ISBLANK(A) returns TRUE or FALSE for every cell (dense). ISDEFINED(A) returns TRUE only where a value exists, BLANK elsewhere (sparse). Reserve ISBLANK / ISNOTBLANK only when every cell must hold an explicit boolean — see skill:using-formula-functions for the rare-valid-exception allow-list.

// WRONG — densifiesIF(ISBLANK('Revenue'), 'Default', 'Revenue')
// CORRECT — sparseIFBLANK('Revenue', 'Default')IFDEFINED('Revenue', 'Revenue')

Structure Formulas Scope-First, Aggregations Last

Put narrowing modifiers and guards before calculations; aggregations after.

// WRONG — computes on all cells, then filters('Revenue' * 'Rate')[FILTER: 'Region' = SET_Selected_Region]
// CORRECT — scope first, then calculate, then aggregate'Revenue'  [FILTER: 'Region' = SET_Selected_Region]  * 'Rate'  [REMOVE: Product]

Pattern: Source [scope modifiers] → calculation → [REMOVE / BY aggregation].

Model Date Ranges with PRORATA

PRORATA(TimeDimension, StartDate, EndDate) returns the fraction of each period within [StartDate, EndDate). Start date is included; end date is excluded; add 1 for inclusive end.

// WRONG — verbose, error-prone, poor sparsityIF('Date' >= 'Start Date' AND 'Date' <= 'End Date', 1, BLANK)
// CORRECT — sparse presence factorPRORATA(Month, 'Start Date', 'End Date' + 1)

Derive presence booleans with ISDEFINED(PRORATA(...)), not ISBLANK(PRORATA(...)).

Prior Period Lookups

Use SELECT with offset for simple lags — not PREVIOUS (iterative, expensive).

PREVIOUS(Month) is correct only for true iterative calculations within a single metric (e.g. cumulative balance, cash roll-forward, inventory carry-forward). For multi-metric iteration (opening/closing inventory across metrics), use PREVIOUSOF(...) with a cycle. See skill:iterating-with-previous-and-cycles for patterns.

Allocation: BY Over ADD

BY follows a mapping (sparse); ADD creates all combinations (dense).

• Use BY CONSTANT with a mapping for value replication instead of ADD CONSTANT (dense).
• Do not wrap BY with IF(ISBLANK(...)) guards on dimension-typed metrics.
• See anti-pattern table for ADD+FILTER and round-trip patterns.

Access Rights

IFDEFINED('Users roles', ...) wrapping is mandatory for access rights formulas. IFDEFINED(User, ...) is an optional additional trim — not a substitute for IFDEFINED('Users roles', ...).

// Mandatory wrappingIFDEFINED('Users roles', 'Can Edit'[BY: User])
// Optional additional trim layered on topIFDEFINED(User, IFDEFINED('Users roles', 'Can Edit'[BY: User]))

Use BLANK (not FALSE) for denied access to preserve sparsity.

Conditional Creation and Subsetting

Use IF(condition, value) to create values sparsely — [ADD: X][FILTER: condition] densifies first, then subsets.

• Use FILTER only on already-computed values; prefer [FILTER: CurrentValue] over IF(expr, expr, BLANK).
• Use EXCLUDE instead of FILTER: NOT(...).
• When the goal is to pick one dimension item and drop the dimension, use SELECT (aggregates it away) rather than FILTER (keeps dimension in output).
• Division by zero: Pigment handles it natively (returns BLANK); do not guard with IF(x <> 0, a / x).

Fix These Anti-Patterns Before Delivery

Scan every formula for these patterns. Each row is a rewrite trigger.

Remember: BLANK = undefined = not stored. FALSE ≠ BLANK (stored, densifies). 0 ≠ BLANK (stored). Always prefer absence over explicit empty values unless business logic demands a stored zero or boolean.

Related Skills

• skill:diagnosing-performance-issues — profiler-based troubleshooting of delivered formulas
• skill:iterating-with-previous-and-cycles — iterative calculation optimization
• skill:using-formula-functions — ISBLANK rare-valid-exception allow-list