Exists (PQL)

Returns the set of members of one set that exist with one or more members of one or more other sets.

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

Syntax

Exists( <List> , <List> )

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

Comments
  • The first argument represents the initial listing of elements from a given hierarchy.
  • The second argument can be composed of 3 different types of items:
    • Another list of elements from the same model, These are logically not from the same hierarchy as the first listing.
    • A data point from the same model.
    • A 'list of lists' or "cross-joined" lists built from 2 or more hierarchies from the same model.
  • If no measure is used in the second argument, the default measure for the model is used.
  • For details on how to employ and use this function see the semantic calculation overview.

The Exists function is useful for building sets or lists based on members or tuples that also exist in other lists. It operates by finding items in the first list that either exist are not empty when crossed against the selections and items in the second listing.

Non-Empty vs Exists

This function is like the MDX exists function; it retrieves the members of an initial list that are found with in the secondary list. It is also closely related to the non-empty function.

Unlike Non-Empty, the Exists doesn't use necessarily use measures to resolve itself. If you check product category to product it will simply run the join between those tables to find a relationship between the selections. Non-empty, on the other hand, will force a measure and check for non-empty values. The Exist function will add joins to fact tables only if the sets are on different, not-directly-related tables (or explicitly requested).

Examples

This example retrieves a list of countries that have non-empty values based on the list of top 3 manufacturers (using the cost metric).

Exists( AllMembers([customer].[country]), TopCount(AllMembers([manufacturers].[manufacturer]),3, [Measures].[Cost]) )

 

This example retrieves a list of countries that have non-empty values based on bikes and the sales metric.

Exists( AllMembers([customer].[country]), ([products].[bikes], [Measures].[sales]) )