%FIRST (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
