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.

Start here if…

• You want to filter a node to specific members (e.g., EMEA/APAC only).

• You want to pick one value per group (e.g., first per Product or Customer).

• You want to fill missing members 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: keep only rows that match a member list or condition.

• Pick: select one row/value per group (reduce within a grouping).

• Fill: add missing members or combinations (create completeness).

• Rank: generate helper indices based on order or dimension order.

• Map: translate a value via a lookup/threshold table.

Common patterns

• Filter to a member list → FILTER('Node', "Level", ["A", "B"])
  Use when you want a subset by explicit members.

• Filter using a comparison between members → 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 values for all level values to exist.

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

• Fill missing values after reshaping → FILL_NA('Node')
  Use when pivoting/reshaping creates missing cells and you want to explicitly close those gaps.

• Mark missing combinations between two nodes → FINDMISSING('Node1', 'Node2')
  Use when you want a diagnostic helper to show mismatches as 1/0.

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

• Lookup from an index/return table → LOOKUP('LookupValue', 'Index', 'ReturnValue')
  Use when you need threshold (approximate) mapping from one measure to another.

Functions in this category

Choosing between similar functions

• FILTER vs LEVELFILTER

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

  • Use LEVELFILTER when filtering depends on comparing level values (member-to-member logic).

• FILLMISSING vs FILLMISSING_LAST

  • Use FILLMISSING when you want missing values filled using a defined constant.

  • Use FILLMISSING_LAST when you want missing values filled using the last available value.

• FILLMISSING vs FILL_NA

  • Use FILLMISSING when you want missing values added for a dimension/level.

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

• 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 for rules and thresholds.

• FINDMISSING vs IS_NA

  • Use FINDMISSING to compare two nodes and find missing combinations between them.

  • Use IS_NA to detect undefined values in a single node (often used as a diagnostic flag).

Pitfalls & troubleshooting

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

• More rows than expected after filling: confirm you’re filling the intended level (and understand whether you’re adding filling values with a constant or the last available value).

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

• Missingness diagnostics: use IS_NA for single-node undefined-value flags.

Related sections

• Formula basics notation, lists, and quoting rules used in filters

• Operators comparisons and boolean logic commonly used with filtering

• Dimensionality & hierarchies reshape functions often used before/after filtering

• Troubleshooting guide empty results and missing value behavior