FILTER modifier

  • 21 December 2021
  • 0 replies
  • 3593 views

Userlevel 5
Badge +6
  • Community Manager
  • 20 replies

Description

Filters data from a Block based on cell values or associated items.

 

Syntax

expression[FILTER: filtering_expression]

 

Filtering_expession being a boolean expression.  

 

Return type

same as expression

 

Examples

Case

Results

Filtering off of Multiple Conditions 
Revenue[FILTER: Country = Country."FR" AND Product = Product."Product 1"] Returns the Revenue of France for Product 1

Revenue[FILTER: Country = Country."FR" OR Country = Country."UK"]

Returns the Revenue of France and UK.

 Filtering off of defined values

Revenue[FILTER: CurrentValue > 1000]

or

Revenue[FILTER: Revenue > 1000]

Returns the Revenue by its original Dimensions only if the Revenue is higher than 1000.

Revenue[FILTER: Cost > 1000]

Returns the Revenue by its original Dimensions only if the Cost is higher than 1000.

Filtering off of data in another Metric 

Revenue[FILTER: 'Filter Metric']

Returns the Revenue by its original Dimensions only if the boolean Filter Metric is set to true.

 

Filtering on values with “CurrentValue” 

 

Filtering based on the 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 offers a specific keyword:  CurrentValue. This keyword represents the expression on which the filter is applied but in a much shorter and readable way, avoiding copy-paste mistakes.  

The previous formula can be written: 

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

 

 

When to use EXCLUDE vs FILTER

 

The FILTER and EXCLUDE modifiers have very similar functionalities just an opposite approach. When using FILTER you are defining which data to bring, while EXCLUDE you are defining which data not to bring in.   

[FILTER: NOT condition OR ISBLANK(condition)]  is the same as   [EXCLUDE: condition]

The difference is how they handle Blanks. Given the scenario above, it is recommended to use EXCLUDE for readability and performance issues.   

 

Additional examples in the article: Filtering data in Formulas

 

🎓 To learn more about how to use FILTER and EXCLUDE modifiers, visit our Academy.

Excel equivalent: none

See also: BY, ADD, REMOVE, EXCLUDE


This topic has been closed for comments