%FIRST (MDX)

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)

Where:

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, the system 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, the system 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.

Example

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

%FIRST (MDX)

Returned Type

Syntax and Details

Example

See Also