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 and 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

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.

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 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 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).

2. 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)

3. 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 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 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:

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

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]

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, LAST, FIRSTNONBLANK and 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 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.

🎓 Pigment Academy

See the Academy’s Feature Spotlight video guide on Modeling Hints, or take courses on Work with Dimensionality in Formulas and the Interactive Source to Target Mapping Tool.