# What the MDX Axis() Function Actually Returns

A month or so ago, before I went on holiday, I was working on a really cool MDX idea that involved the Axis() function. Unfortunately I’ve forgotten what that idea was but while I was working on it I did find out something interesting about the Axis() function – namely that it doesn’t do exactly what the documentation says it does.

The documentation says that the Axis() function returns the set of tuples on a given axis in an MDX query. Here’s a simple example query on the Adventure Works cube showing it in action:

WITH
MEMBER MEASURES.TEST AS SETTOSTR(AXIS(1))
SELECT {MEASURES.TEST} ON 0,
[Customer].[Gender].MEMBERS ON 1
FROM

Here, I’m using the SetToStr() function to take the set returned by the Axis() function and display it in a calculated measure. As you can see from the screenshot, I’m showing all three members from the Gender hierarchy on the Customer dimension on rows and the set returned by Axis(1) is indeed that set.

BUT, now look at this second query and what it returns:

WITH
MEMBER MEASURES.FIRSTMEMBER AS
MEMBERTOSTR(AXIS(1).ITEM(0).ITEM(0))

MEMBER MEASURES.TEST AS
IIF(
[Customer].[Gender].CURRENTMEMBER.UNIQUENAME =
MEASURES.FIRSTMEMBER, NULL, 1)

SELECT MEASURES.TEST ON 0,
NON EMPTY
[Customer].[Gender].MEMBERS ON 1
FROM

Why is this interesting? The calculated measure FIRSTMEMBER returns the unique name of the first member in the set returned by Axis(1), which should be the first member shown on the rows axis. The calculated measure TEST returns null if the currentmember on the Gender hierarchy has the same unique name as the member returned by FIRSTMEMBER. The calculated measure TEST is on columns in the query, and on rows we get all the members on the Gender hierarchy that return a non null value for TEST. Since only Female and Male are returned, the All Member on Gender must return null for TEST, which means that the All Member is the first member in the set returned by the Axis() function.

So, to summarise, the Axis() function actually returns the set of members on an axis the current query before any NON EMPTY filtering is applied.

### 2 responses

1. another interesting thing happens if you replace the NON EMPTY-keyword with the NONEMPTY()-function
this returns all 3 rows and the first row has a value of NULL

so it seems that there are different dependencies between AXIS() and NON EMPTY / NONEMPTY() and how/when they are evaluated

any thoughts on that?

-gerhard

• Gerhard, I would expect those differences: NON EMPTY is a part of the SELECT statement, and evaluated at a different time to functions on the axis declaration like NONEMPTY().

This site uses Akismet to reduce spam. Learn how your comment data is processed.