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.
This function returns a number.
Syntax and Details
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.
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