FILTER
Category: Filtering & data shaping
Overview
The FILTER function returns only the rows of a node that match a specified condition. Rows that do not satisfy the filter condition are removed from the result.
Use this function to restrict calculations to a subset of your data, for example by selecting specific years, regions, or products.
Syntax
FILTER('Node', "Level", FilterValue [, "FilterOperation"])
FilterValue can be a single level value in double quotes or a list of level values in square brackets.
Example usage: FILTER('Sales', "Year", "2025")
Parameters
| Parameter | Description | Type | Required | Default |
|---|---|---|---|---|
| Node | Input node, specified in single quotes (e.g. 'Revenue') | Node reference | Yes | — |
| Level | The level by which the input node shall be filtered, specified in double quotes (e.g. "Year", "Region") | Level name | Yes | — |
| FilterValue | The value(s) to filter by. A single level value or a list of level values (e.g. "2026" or ["EMEA", "APAC"]) | Level value / Level value list | Yes | — |
| FilterOperation | Defines how values are compared to the filter. Valid values:"EQ", "NEQ", "LT", "LTE", "GT", "GTE" | String | No | "EQ" (equal) |
See also: Comparisons & boolean operators
Output Shape
| Aspect | Behavior |
|---|---|
| Dimensionality | Same dimensions as the input node |
| Values | Only rows fulfilling the filter condition are returned |
| Row count | Reduced, non-matching rows are removed |
Watch Out
- FILTER removes rows that do not match the filter condition.
- When using a list of values, rows matching any value in the list are kept.
- Filters can be nested to restrict multiple dimensions.
- The level specified in the filter must exist in the input node.
Examples
Input node: Sales
| Year | Product | Value |
|---|---|---|
| 2025 | Alpha | 100 |
| 2025 | Beta | 200 |
| 2026 | Alpha | 150 |
| 2026 | Beta | 300 |
| 2027 | Alpha | 10 |
Filter to a single value
Keep only rows for 2025.
Formula: FILTER('Sales', "Year", "2025")
| Year | Product | → FILTER Result |
|---|---|---|
| 2025 | Alpha | 100 |
| 2025 | Beta | 200 |
Exclude specific values with NEQ (not equal)
Return all rows except 2025 and 2026.
Formula: FILTER('Sales', "Year", ["2025", "2026"], "NEQ")
| Year | Product | → FILTER Result |
|---|---|---|
| 2027 | Alpha | 10 |
Chaining filters
You can nest FILTER calls to narrow down on multiple dimensions at once.
Formula: FILTER(FILTER(‘Sales’, “Year”, “2026”), "Product", "Alpha")
| Year | Product | → FILTER Result |
|---|---|---|
| 2026 | Alpha | 150 |
Using a Project Variable as the filter value
Instead of hardcoding a level value for a specific year, you can use a project variable so the filter updates automatically when the variable changes.
Formula: FILTER('Sales', "Year", "$FC_End")
This returns only rows where Year equals the current value of $FC_End, no formula change needed when the planning horizon shifts.
See also: Project Variables
Related Functions
| Function | When to use instead |
|---|---|
| IF | Apply conditional logic without removing rows. |
| LEVELFILTER | When the condition depends on comparing level values rather than matching explicit values. |
| EXPANDSINGLE | Expand to specific level values instead of removing rows. |
| DATA | Load source data before filtering. |