---
title: "Performance Improvement Hints"
slug: "performance-hints"
updated: 2026-04-02T15:51:41Z
published: 2026-04-02T15:51:41Z
stale: true
---

> ## Documentation Index
> Fetch the complete documentation index at: https://kb.pigment.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Performance Improvement Hints

The formula bar provides hints that help you improve performance and understand automatic adjustments that Pigment has made on your formulas.

In some cases it warns about missing or unnecessary Dimensions, so that you can take appropriate action. In other cases it helps users’ understanding by providing context.

This page covers hints flagging densifying expressions. For other kinds of hints, see [Dimensionality Hints](/v1/docs/dimension-alignment-indicator) and [Explanatory Hints](/v1/docs/explanatory-hints).

## Find the hints

First, ensure **Show performance improvement hints** is switched on for your Application:

1. In your Application, open any Block.
2. If no formula bar is visible, select ***⨍***, the **Show formula bar** button in the top toolbar.
3. Select the three-dot menu to the right of the formula bar.
4. Select **Show performance improvement hints** from the drop-down that opens.

> [!NOTE]
> ℹ️ Note
> 
> The **Show performance improvement hints** setting is per user, based on browser local storage. Each user needs to set it in their Application.

Hints are available when 🛈 appears beside the ***⨍*** indicator or a speed gauge symbol appears by a line in the formula bar. See the example below.

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/image(298).png)

## Functions giving rise to hints

Performance improvement hints alert users when a formula is likely to slow down performance of Pigment, normally due to unnecessary data density. See [Leverage sparsity for optimal performance](/v1/docs/leverage-sparsity-for-optimal-performance) for information on density and sparsity.

The speed gauge symbol appears beside the formula line triggering the hint. Select it to open a pop-up containing more information.

The functions that can result in performance improvement hints are as follows:

### ISNOTBLANK

**Use**`ISDEFINED`**over**`ISNOTBLANK`**to check whether a value exists**

`ISNOTBLANK` returns TRUE or FALSE for every cell — storing an explicit FALSE wherever a value is absent. This densifies the result completely. `ISDEFINED` returns TRUE where a value exists and BLANK elsewhere, so undefined cells are kept sparse.

Use `ISDEFINED` whenever you only need to know whether a value exists. Only keep `ISNOTBLANK` when downstream logic specifically requires an explicit FALSE rather than BLANK.

**Examples**:

1. Boolean metric: `ISNOTBLANK(Revenue)` should be rewritten as `ISDEFINED(Revenue)`
2. Conditional logic: `IF(ISNOTBLANK(Revenue), Revenue * 1.1)` should be rewritten as `IFDEFINED(Revenue, Revenue * 1.1)`
3. Fallback logic: `IF(ISNOTBLANK(Revenue), Revenue, Forecast)` should be rewritten as `IFBLANK(Revenue, Forecast)`

For more information on using ISNOTBLANK, see the [ISNOTBLANK function](/v1/docs/isnotblank-function) article.

### ISBLANK

**Performance tip: avoid**`ISBLANK`**when checking whether a value exists**

`ISBLANK(Value)` returns `TRUE` or `FALSE` for every cell in the output with the Dimensions of argument `Value`. This can densify the output and increase how many cells require calculation downstream. If your goal is simply to check whether a value exists, use `ISDEFINED(Value)` and invert the logic with `NOT(...)` if necessary.

**Examples:**

1. **"Value is blank" as a condition**

Don't try to express this as a standalone Boolean. Instead use the `EXCLUDE` modifier to scope your logic.

Instead of: `IF(ISBLANK(MyMetric), <something>)`, use `IF(OtherCondition [EXCLUDE: MyMetric], <something>)`

If you want to skip calculation where a value is absent: `IF(ConditionA AND ISBLANK(MyMetric), ...)` becomes `IF(ConditionA [EXCLUDE: MyMetric], ...)`

If you genuinely need to branch on blank vs. non-blank, use `IFBLANK` or `IFDEFINED` directly — don't materialize the blank check as a Boolean first (see examples 2 and 3 below).

1. **Only keep rows where a value exists**

Use ISDEFINED instead of ISBLANK to keep rows where a value exists.

`IF(NOT(ISBLANK(MyMetric)), MyMetric)` becomes `IFDEFINED(MyMetric, MyMetric)`

1. **Fill missing values**

Instead of: `IF(ISBLANK(MyMetric), 0, MyMetric)` or `IF(NOT(ISDEFINED(MyMetric)), 0, MyMetric)`, use `IFBLANK(MyMetric, 0)`

For more information on using ISBLANK, see the [ISBLANK function](/v1/docs/isblank-function) article.

### IF

**Avoid densifying your output with a constant in the "else" value**

The “else” value, meaning the third argument (`Value if False or BLANK`) in `IF(Logical Test, Value if True [, Value if False or BLANK])` is optional. When you provide a fixed "else" value like `0` or `FALSE`, the formula stores that value for every cell where the logical test returns **False**or **BLANK**. For example, if `Revenue` is defined for only 500 out of 10,000 cells, `IF(Revenue > 1000, TRUE, FALSE)` stores a result in all 10,000 cells: FALSE where Revenue ≤ 1000, and also FALSE where Revenue doesn't exist at all. This densifies the output and increases how much data Pigment has to store and recompute.

If you don't truly need a default, omit the third argument so Pigment returns `BLANK` instead:

- `IF(Revenue > 1000, TRUE, FALSE)` → stores TRUE or FALSE for every cell → **dense**
- `IF(Revenue > 1000, TRUE)` → stores TRUE only where the condition is met, BLANK everywhere else → **sparse**

When the TRUE branch simply returns the Metric being tested, we recommend using `FILTER` over `IF` , as it evaluates the expression once per cell rather than twice (once in the condition, once in the TRUE branch):

- `IF(Revenue > 1000, Revenue)` → evaluates `Revenue` twice per matching cell
- `Revenue[FILTER: CurrentValue > 1000]` → evaluates once, then filters → faster on large Metrics

For more information on using IF, see the [IF function](/v1/docs/if-function) article.

### FIRST/LAST

**Use**`FIRSTNONBLANK`**/**`LASTNONBLANK`**instead of**`FIRST`**/**`LAST`**when possible**

When `FIRST` or `LAST` is used as an aggregation method (e.g. `Metric[REMOVE LAST: Dimension]`), Pigment calculates a row number ranking over the entire Dimension, including blank cells. This densifies the calculation, even over those with no data.

`FIRSTNONBLANK` and `LASTNONBLANK` avoid this computation, so only defined cells are computed.

If the first (or last) non-blank value in your Dimension happens to be the same as the absolute first (or last) value, meaning there are no leading (or trailing) blanks, you can safely replace `FIRST` with `FIRSTNONBLANK` or `LAST` with `LASTNONBLANK` without changing the result.

`LAST` and `LASTNONBLANK` frequently return different values when blank cells exist in the middle or end of the dimension, so verify your Dimension values before making the change.

Here are examples highlighting when the substitution is safe and when it is not, using a `Budget` Metric using Dimensions of **Product**and**Month**:

---

**The data:**

|  | **Jan** | **Feb** | **Mar** | **Apr** |
| --- | --- | --- | --- | --- |
| Product A | 100 |  |  | 200 |
| Product B |  | 50 | 75 |  |

---

`[REMOVE LAST: Month]`**vs**`[REMOVE LASTNONBLANK: Month]`

|  | **LAST** | **LASTNONBLANK** |
| --- | --- | --- |
| Product A | 200 (Apr) | 200 (Apr) ← same |
| Product B | BLANK (Apr is blank) | 75 (Mar) ← different |

Product A is safe to substitute — its last month (Apr) is non-blank so both return the same value. Product B is **not** safe — `LAST` returns blank because Apr is empty, while `LASTNONBLANK` reaches back to Mar.

---

`[REMOVE FIRST: Month]`**vs**`[REMOVE FIRSTNONBLANK: Month]`

|  | **FIRST** | **FIRSTNONBLANK** |
| --- | --- | --- |
| Product A | 100 (Jan) | 100 (Jan) ← same |
| Product B | BLANK (Jan is blank) | 50 (Feb) ← different |

Product A is safe to substitute — its first month (Jan) is non-blank so both return the same value. Product B is **not** safe — `FIRST` returns blank because Jan is empty, while `FIRSTNONBLANK` reaches forwards to Feb.

---

**A case where the substitution is reliably safe**

If you have a planning Metric where every row is guaranteed to have a value in the current month (e.g. a budget that's always fully populated for the latest period), then the following formulas are equivalent:

'Monthly_Budget'[REMOVE LAST: Month] = 'Monthly_Budget'[REMOVE LASTNONBLANK: Month]

---

**A common real-world use: "last known value" carry-forward**

Here `LASTNONBLANK` is both the accurate choice and the more performant one.

For example, to calculate each employee's most recent salary entry per year: `'Salary'[BY LASTNONBLANK: Month.'Year']`

Using `LAST` here would return BLANK for any year where December has no salary entry, which is almost certainly wrong, as well as slower.

For more information on these functions, see the following guidance: [FIRST](/v1/docs/by-modifier#first), [LAST](/v1/docs/by-modifier#last), [FIRSTNONBLANK](/v1/docs/by-modifier#firstnonblank) and [LASTNONBLANK](/v1/docs/by-modifier#lastnonblank).

### ADD

**Performance tip: use**`BY`**or**`IF`**instead of**`ADD`**where possible**

`ADD` applies a value across **every Item** of the added Dimension, resulting in dense output. In a model with 10,000 employees and 24 months, `Budget[ADD: Employee]` creates 240,000 cells even if only 3,000 employees are active.

As much as possible try to allocate the data only to the relevant Item of the new Dimension.

`ADD` is only appropriate as a last resort when no mapping exists **and** you genuinely need all Dimension combinations. For example, `'Global Rate'[ADD: Version]` where every Version really does get the same value and there is no Version hierarchy to go through.

**Example**:

I have a **Learning Stipend Budget** Metric defined on the Department Dimension and an Employee Dimension with a Department Property. if you want to create a Metric structured on both Employee and Department where the budget is allocated to the correct combination of Employee x Department, here is a non-performant version using `ADD`:

`'Learning Stipend Budget'[ADD: Employee][FILTER:Department = Employee.Department]`

instead you should use:

`'Learning Stipend Budget'[BY CONSTANT: -> Employee.Department]`

For more information on using `ADD`, see the [ADD modifier](/v1/docs/add-modifier) article.

### ADD FILTER

**Use**`IF`**instead of**`[ADD][FILTER]`**for conditional value creation**

The pattern `expression[ADD: Dimension][FILTER: condition]` first applies the value across **every Item** of the added Dimension, creating a fully dense result, and then discards the cells that don't match the FILTER condition. `IF` avoids that entirely, as cells where the condition is FALSE or blank are never materialized at all.

For example, the below formula is not performant:

`10[ADD: Month][FILTER: Month > Month."Jan 25"]`

This is because it creates all Month cells, then discards most in the filter condition.

We recommend instead this pattern:

`IF(Month > Month."Jan 25", 10)`

This only creates cells where the condition is TRUE.

This applies specifically to **conditional creation** (generating new values based on a condition). If you're filtering down an expression you're already computing, this pattern could work better than `IF`: `Revenue[FILTER: CurrentValue > 1000]`. It calculates once, then applies the filter, which can lead to faster computation on large Metrics.

> [!TIP]
> 🎓 Pigment Academy
> 
> See [the Academy](https://academy.pigment.com/master-formula-logic-with-dimensionality-hints/420231/scorm/2zc322p2wxq6u)’s Feature Spotlight video guide on Modeling Hints, or take courses on [Work with Dimensionality in Formulas](https://academy.gopigment.com/work-with-dimensionality-in-formulas) and the [Interactive Source to Target Mapping Tool](https://academy.gopigment.com/source-to-target-mapping-tool).