Filtering data in formulas

  • 21 December 2021
  • 0 replies

Userlevel 5
Badge +6
  • Community Manager
  • 20 replies


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.

If you need to filter the View of a Metric while keeping the underlying data, please refer to this documentation: Playing with Views



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




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:




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.




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:




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 duplicatate 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 dupplication the expression returning values. The previous formula can be written: 

(‘Bloc A’ + ‘Bloc B’ + ‘Bloc C’ - ‘Bloc D’)[FILTER: CurrentValue > 1000]




When an expression is written without keywords between brackets, Pigment will consider it a filtering operation.

For example, Revenue[Revenue > 1000] is the same as Revenue[FILTER: Revenue > 1000].


See also

Function by category



0 replies

Be the first to reply!