Filtering & data shaping

Overview

Use this category when you need to restrict results to a subset, select representative values per group, fill missing combinations, or create helper tables such as ranks and enumerations.

These functions help you shape the result set without changing overall dimensionality.

Start here if…

• You want to filter a node to specific level values, for example EMEA or APAC only.

• You want to pick one value per group.

• You want to fill missing level values or combinations so calculations don’t break.

• You need ranking / enumeration helpers for further logic.

Not here if…

• You need to reshape dimensionality → see Dimensionality & hierarchies

• You need time comparisons or time-based logic → see Compare periods or Rollforward & time series

Mental model

• Filter functions keep only rows that match a level value or condition

• Pick functions select one value per group

• Fill functions add missing level values or combinations

• Rank functions generate helper indices based on value order or dimension order

• Map functions translate values through a lookup or threshold table

Common patterns

Filter to a list of level values
FILTER('Node', "Level", ["A", "B"])
Use when you want a subset based on explicit level values.

Filter by comparing level values
LEVELFILTER('Node', "Level1", "Level2")
Use when the filter depends on comparing one level value to another.

Pick the first value per group
FINDFIRST('Node')
Use when you need a single representative value per group.

Fill missing level values for a dimension
FILLMISSING('Node')
Use when you want all level values of a dimension to be present.

Fill missing with last available value
FILLMISSING_LAST('Node')
Use when you want to carry the previous value forward.

Fill missing values after reshaping
FILL_NA('Node')
Use when reshaping or pivoting created missing cells that should be filled explicitly.

Mark missing combinations between two nodes
FINDMISSING('Node1', 'Node2')
Use when you want a diagnostic flag for mismatched combinations.

Create a rank or enumeration helper
ENUM('Node') or ENUM_LEVEL("Level")
Use when you need indices for sorting, thresholds, or rule logic.

Map values through a lookup table
LOOKUP('LookupValue', 'Index', 'ReturnValue')
Use when you need threshold-based mapping from one measure to another.

Functions in this category

Choosing between similar functions

FILTER vs LEVELFILTER

• Use FILTER when you filter by explicit level values or simple conditions on one level.

• Use LEVELFILTER when the filter depends on comparing level values.

FILLMISSING vs FILLMISSING_LAST

• Use FILLMISSING when you want to add missing values using a defined fill behavior.

• Use FILLMISSING_LAST when you want to fill gaps with the last available value.

FILLMISSING vs FILL_NA

• Use FILLMISSING when you want to add missing values for a dimension or level.

• Use FILL_NA when reshaping created missing cells and you want to fill only those cells.

FINDFIRST vs ENUM / ENUM_LEVEL

• Use FINDFIRST when you want to select one value per group.

• Use ENUM / ENUM_LEVEL when you want an index or ordering helper.

FINDMISSING vs IS_NA

• Use FINDMISSING when you want to compare two nodes and detect missing combinations between them.

• Use IS_NA when you want to detect undefined values in a single node.

Pitfalls & troubleshooting

• FILTER returns empty: confirm level values match exactly (spelling/case) and the referenced level is correct.

• More rows than expected after filling: confirm that you are filling the intended dimension or level.

• Unexpected missing values after reshaping: use FILL_NA or a diagnostic helper node to inspect missing intersections.

• Single-node missingness checks: use IS_NA when you need a diagnostic flag for undefined values.

Related sections

• Dimensionality & hierarchies: reshape functions often used before or after filtering

• Logical functions: comparisons and condition logic used together with filtering

• Troubleshooting guide: empty results and missing value behavior

• Function catalog: full signatures, parameters, and examples