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

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:

Filtering values

It is also possible to filter on values to only keep certain cells that match the filtering expression.

You can also define complex filtering expressions:

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]

See also

Function by category