CURRENTMEMBER function gives an error in MDX

Question

I have my dimension as below:

I want to get one market at a time.

When I use this query, I am getting the list of all the members as I am using .MEMBERS function:

SELECT [MARKET BASE].[Market Base].[Market Base].MEMBERS ON 1,
[Measures].[% OTC Sales] ON 0
FROM [PharmaTrend Monthly Ext];

But when I use the following query to get only the current member then I get an error saying: The CURRENTMEMBER function expects a hierarchy expression for the 1 argument. A member expression was used.

SELECT [MARKET BASE].[Market Base].[Market Base].CURRENTMEMBER ON 1,
[Measures].[% OTC Sales] ON 0
FROM [PharmaTrend Monthly Ext]; 

UPDATE:

When I use the below query, I get the result with All member:

WITH 
MEMBER [Market] AS
    [MARKET BASE].[Market Base].[Market Base].CURRENTMEMBER

SELECT [Measures].[% OTC Sales] ON 0,
        [Market] ON 1
FROM [PharmaTrend Monthly Ext];

How can I go about resolving this?

Answer 1

CURRENTMEMBER is implicitly picked if you have any member from the hierarchy in scope and laid out on axis. By default it is the ALL member.

WITH MEMBER [Measures].[Market] AS
[MARKET BASE].[Market Base].CURRENTMEMBER.NAME

SELECT [MARKET BASE].[Market Base].[Market Base].MEMBERS ON 1,
{[Measures].[% OTC Sales], [Measures].[Market]} ON 0
FROM [PharmaTrend Monthly Ext];

This will return the name of the current market selected or on axis(in scope).

Answer 2

Just to be precise chaps - currentmember does not iterate over anything in mdx. None of the mdx I see in the original post is using any sort of iteration.

This function is as close as you get to a loop in mdx:

GENERATE

Also Filter can be thought of as an iterator.

CURRENTMEMBER is generally used in a WITH clause for claculations. What it does is pick up the current member for each member that is in context - this basically means what you see in the result table.