# Determine the first or last member with data

## Summary

In some applications, it is useful to find the first or last dimension member that has data associated with it. This article illustrates how to use the `HEAD()`

, `TAIL()`

, and `UNION()`

functions to return the first and last members of a dimension that have data. The article also illustrates the use of the `NonEmptyCrossJoin()`

function.

*Original product version:* SQL Server

*Original KB number:* 301934

## More information

Assume that your task is to find the first and last members of the time dimension with data from the FoodMart 2000 sample. For many, the first thought for finding the first member with data would be to use the `FirstChild()`

function as follows:

```
SELECT {[Time].FirstChild} ON COLUMNS
FROM SALES
```

Likewise, the first thought for finding the last member of the time dimension with data would be to use the `LastChild()`

function as follows:

```
SELECT {[Time].[1998].[Q4].LastChild} ON COLUMNS
FROM SALES
```

The first multidimensional expression (MDX) query, however, returns the value associated with [1997].[Q1] and not the value associated with [1997], which is the first member with data. The second MDX query returns the value associated with [1997].[Q4].[12], which is the last member of the dimension, but not the last member with data.

As an alternative, the `HEAD()`

function returns the first specified number of elements in a set, and can be used to return the first member of the dimension. Likewise, the `TAIL()`

function returns a subset from the end of a set and can be used to return the last member of the dimension. The MDX query to return the first member of the time dimension would take the following form:

```
SELECT HEAD([Time].Members,1) ON COLUMNS
FROM SALES
```

This query does return 1997 as the first member of the dimension with data.

The MDX query to return the last member of the dimension would take the following form:

```
SELECT TAIL([Time].Members,1) ON COLUMNS
FROM SALES
```

This MDX query returns [1998].[Q4].[12] as the last member of the dimension. However, the member returned is not the last member of the dimension with data. In order to eliminate members with no data, the `NonEmptyCrossJoin()`

function should be used to filters out all the members in the dimension that don't have data associated with them.

The MDX query to find the first member with data then takes the form

```
SELECT HEAD(NonEmptyCrossJoin([Time].Members,1),1) ON COLUMNS
FROM SALES
```

and the MDX query to find the last member with data then takes the form:

```
SELECT TAIL(NonEmptyCrossJoin([Time].Members,1),1) ON COLUMNS
FROM SALES
```

The `UNION()`

function can then be used to combine the two MDX queries into a single query:

```
SELECT
UNION(HEAD(NonEmptyCrossJoin([Time].Members,1),1), TAIL(NonEmptyCrossJoin([Time].Members,1),1)) ON COLUMNS
FROM SALES
```