Skip to main content

This is documentation for Caché & Ensemble. See the InterSystems IRIS version of this content.

For information on migrating to InterSystems IRIS, see Why Migrate to InterSystems IRIS?


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

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

See Also