--- title: "Filter data in formulas" slug: "filter-data-in-formulas" description: "Learn how to use the FILTER and EXCLUDE modifiers in Pigment to effectively filter data based on specific values and enhance your analysis." tags: ["Boolean Expressions", "Data Filtering", "Modeling Formulas", "Pigment Modifiers", "Reporting Data Visualization "] updated: 2025-05-28T10:00:01Z published: 2025-08-22T12:00:18Z --- > ## 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. # Filter data in formulas When referencing objects, you may want to filter data to keep only certain items of list or values. In that case, you will need to use the modifier `FILTER`. The EXCLUDE modifier in Pigment allows you to filter out specific data from a Block based on cell values or associated items. This is particularly useful when you want to remove certain values or items from your analysis. For more information, see [EXCLUDE modifier](/v1/docs/exclude-modifier). ## Syntax of a filtering operation The `FILTER` operation requires a Boolean expression: `expression[FILTER: filtering_expression]` This `filtering_expression` can filter data based on**Dimension items** (filtering Countries, Employees, Product, etc.) or on **values** (filtering quantities lower than a certain amount, etc.) ## Filtering Items of Lists The filtering expression can take single Items of Lists: `List = List."Item"List <> List."Item"` It works also with range of items: `List IN (List."Item1", List."Item2", ..., List."ItemX")` ## Examples If the source Metric or Property you want to use in a formula is based on a List (Country for example) where you only need one or a few items (FR and UK for example), then you may use this syntax to reference items of Lists in the filtering expression: | **Case** | **Returns** | | --- | --- | | `Product.Price[FILTER: Product.Category = Category."A"]` | Returns the `Price` of all the Products included in the `Category` `"A"` | | `Revenue[FILTER: Country = Country."FR" OR Country = Country."UK"]` | Returns the `Revenue` of France and UK. | | `Transactions.Costs[FILTER: FIND("ABC", Transactions.Supplier) > 0]` | Returns the `Cost` Property of the `Transactions` that contains `"ABC"` in the `Transactions` `Supplier` name. | ## Filtering values It is also possible to filter on values to only keep certain cells that match the filtering expression. | **Case** | **Returns** | | --- | --- | | `Product.Price[FILTER: Product.Price > 10]` | The Price per Product only if the `Price` is higher than 10. | | `Revenue[FILTER: Revenue > 1000]` | The `Revenue` by its original Dimensions only if the `Revenue` is higher than 1000. | You can also define complex filtering expressions: | **Case** | **Returns** | | --- | --- | | `Revenue[FILTER: Revenue[by: country.region] > 1000]` | The `Revenue` by its original Dimensions only if the aggregated `Revenue` by `Region` is higher than 1000. | | `Transactions.Amount[FILTER: FIND("ABC", Transactions.Supplier)<>0]` | Returns the `Amount` per `Transactions`, only if the `Transaction` `Supplier` contains `ABC` in its default Property. | ## Filtering values with CurrentValue Filtering based on values of the “expression” is a common use case. The simplest way of doing it is to duplicate the expression in the filtering expression. ex: `Revenue[FILTER: Revenue > 1000]` However, sometimes the expression filtered can be long. ex: `(‘Bloc A’ + ‘Bloc B’ + ‘Bloc C’ - ‘Bloc D’)[FILTER: (‘Bloc A’ + ‘Bloc B’ + ‘Bloc C’ - ‘Bloc D’) > 1000]` Pigment also offers a specific keyword: `CurrentValue` to avoid duplicating the expression. The previous formula can be written: `(‘Bloc A’ + ‘Bloc B’ + ‘Bloc C’ - ‘Bloc D’)[FILTER: CurrentValue > 1000]` > [!NOTE] > ℹ️ **Note** > > When an expression is written without keywords between brackets, Pigment considers it a filtering operation. > > For example, `Revenue[Revenue > 1000]` is the same as `Revenue[FILTER: Revenue > 1000]`. ## **Learn more** - [Function by category](/v1/docs/functions-by-category)