Skip to main content

This is documentation for Caché & Ensemble.

For information on converting to InterSystems IRIS, see the InterSystems IRIS Adoption Guide and the InterSystems IRIS In-Place Conversion Guide, both available on the WRC Distributions page (login required).

Previous sectionNext section


Returns the value of the given measure (or other numeric expression) evaluated for the first non-empty member of a set. This function is an InterSystems extension to MDX.

Returned Type

This function returns a number.

Syntax and Details

%FIRST(set_expression, optional_numeric_expression)


  • set_expression is an expression that evaluates to a set, typically a set of members or tuples.

  • optional_numeric_expression is a numeric-valued expression that the function evaluates for each set element.

    Typically, this expression has the form [MEASURES].[measure_name]

    If you do not specify a numeric expression, DeepSee uses the measure used by the current result cell. For example, this might be the measure used on the 0 axis or the measure specified in the WHERE clause, if any. If the query itself does not specify a measure, DeepSee instead uses %COUNT, which counts records in the fact table.

The %FIRST function returns the first non-missing value evaluated for each member of the given set.


For reference, the following query shows patients with asthma, grouped by birth decade:

SELECT MEASURES.[%Count] ON 0, birthd.decade.MEMBERS ON 1 FROM patients WHERE diagd.asthma
                             Patient Count
 1 1910s                                  *
 2 1920s                                  *
 3 1930s                                  1
 4 1940s                                  9
 5 1950s                                  8
 6 1960s                                 11
 7 1970s                                 12
 8 1980s                                 14
 9 1990s                                 11
10 2000s                                 14
11 2010s                                  4
Copy code to clipboard

The following query uses %FIRST to get the first non-empty set of patients from the preceding set:

SELECT MEASURES.[%Count] ON 0, %FIRST(birthd.decade.MEMBERS) ON 1 FROM patients WHERE diagd.asthma
                             Patient Count
FIRST                                     1
Copy code to clipboard

See Also