Filter (PQL)

Returns a filtered list of elements derived from the specified list using conditional expressions.

  • Returned Output: List
  • Library: PQL \ Semantic \ List
  • Version: 2018.00.000
  • Compatibility: Pyramid Query Language (PQL) data sources

Syntax

Filter( <List> , <Criteria> )

* Click on the function's arguments above for more details on the input values.

Comments
  • List parameter should be a list or set of elements from an existing hierarchy in the model.
  • The selection criteria should be one or more expressions that evaluate to TRUE or FALSE per element in the list.
  • For details on how to employ and use this function see the semantic calculation overview.

This function is like the MDX filter function; it filters members from the specified list.

How It works

The filter functions works by evaluating the criteria expression for each element in the list. Those that evaluate to true are kept, while those that evaluate to false are discarded. As such, the criteria are typically data points that are combined with each element in the evaluation process. These are then compared against some other value using comparison operators to determine a true or false outcome. Composite criteria are fully supported and provide tremendous scope for building complex filters.

See the comparison operators for more information on building criteria.

Examples

This example filters all the members in the Manufacturer attribute, keeping only those manufacturers with sales over 50,000. Notice that the evaluation takes each manufacturer, blends it with the Sales measure to generate a (manufacturer, sales) data point to be used in the evaluation criteria:

Filter( AllMembers([manufacturers].[manufacturer]), ([Measures].[Sales])> 50000 )

The next example filters all the members in the Manufacturer attribute, keeping only those manufacturers with Bike Sales greater than Discount Expenses. Notice that the evaluation takes each manufacturer, blends it with the left data point ( sales and bikes), and compares it to the right data point (expenses and discounts):

Filter( AllMembers([manufacturers].[manufacturer]), ([Measures].[Sales], [Product].[product category].[bikes])> ([Measures].[Expenses], [Promotions].[promotion].[discount]) )

This following example filters all the members in the Manufacturer attribute, keeping only those manufacturers where Bike Costs are equal to 1,000,000 AND where Clothing Sales are less than 300,000. Notice that the "&&" operator is used to perform an "AND" operation to evaluate both criteria statements. If both are true, the outcome is true. If one or both are false, they are both false. Also note that the second statement does not contain a measure; if no measure is stipulated, the default measure for the model is used instead:

Filter( AllMembers([manufacturers].[manufacturer]), ([Measures].[costs], [Product].[product category].[bikes])= 1000000 && ([Product].[product category].[clothing]) < 300000 )