MDX - Retrieving a level of members currently used in where clause

Question

I have a simple data cube with organization structure hierarchy defined. In my calculations inside the cube I would like to have different calculations depending on which level of organization items is currently used in WHERE clause in MDX query.

So let's say that I have 5 levels of organization structure, and for the last level (store level) I would like to change the way that calculation is being made using expression for instance:

IIF([Organization Structure].[Parent Id].LEVEL IS [Organization Structure].[Parent Id].[Level 05], 'THIS IS STORE', 'THIS IS NOT')

This in Visual Studio browser result in something that we actually want:

and same for using MDX Query like:

SELECT { [Measures].[TEST] } ON COLUMNS
FROM [DataCubeName]
WHERE
{
[Organization Structure].[Parent Id].&[123]
}

Problem starts, when we want to use more than one organization structure item in WHERE clause. It is allowed to have items in this clause from the same level only, and I still would like to know which level is it, but of course when we add second item to WHERE like so:

SELECT { [Measures].[TEST] } ON COLUMNS
FROM [DataCubeName]
WHERE
{
[Organization Structure].[Parent Id].&[123],
[Organization Structure].[Parent Id].&[124]
}

I get error that "currentmember failed because the coordinate for the attribute contains a set".

That's why in my expression I have tried to use ITEM(0) function in many different configurations, but I just couldn't find a way to use it on a set of items that are currently used in WHERE clause... So the big question is:

How to get a set of items, that are listed in WHERE clause that is currently being executed so I can use Item(0) on that set, or is there any other way of retrieving Level of currently selected items knowing that they must be the same level?

Answer 1

Using Currentmember combined with set in the where clause is potentially problematic.

See this post from chris Webb: http://blog.crossjoin.co.uk/2009/08/08/sets-in-the-where-clause-and-autoexists/

Here is a possible workaround for your situation: you can try adapting to your curcumstance.

WITH 
  MEMBER [Measures].[x] AS 
    IIF
    (
       (existing [Geography].[Geography].[State-Province].members).item(0).Level
      IS 
       [Geography].[Geography].[State-Province]
     ,'THIS IS state'
     ,'THIS IS NOT'
    ) 
SELECT 
  {[Measures].[x]} ON COLUMNS
FROM [Adventure Works]
WHERE
(
{[Geography].[Geography].[State-Province].&[77]&[FR],
[Geography].[Geography].[State-Province].&[59]&[FR]}
);

Expanding the above to prove it works:

WITH 
  MEMBER [Measures].[x] AS 
    IIF
    (
        (EXISTING 
          [Geography].[Geography].[State-Province].MEMBERS).Item(0).Level
      IS 
        [Geography].[Geography].[State-Province]
     ,'THIS IS state'
     ,'THIS IS NOT'
    ) 
  MEMBER [Measures].[proof] AS 
    (EXISTING 
      [Geography].[Geography].[State-Province].MEMBERS).Item(0).Member_Caption 
  MEMBER [Measures].[proof2] AS 
    (EXISTING 
      [Geography].[Geography].[State-Province].MEMBERS).Count 
SELECT 
  {
    [Measures].[x]
   ,[Measures].[proof]
   ,[Measures].[proof2]
  } ON COLUMNS
FROM [Adventure Works]
WHERE 
  {
    [Geography].[Geography].[State-Province].&[77]&[FR]
   ,[Geography].[Geography].[State-Province].&[59]&[FR]
  };

Results in the following:

So your expression could become something like the following:

IIF
(
    (EXISTING 
      [Organization Structure].[Parent Id].MEMBERS).Item(0).Level
  IS 
    [Organization Structure].[Parent Id].[Level 05]
 ,'THIS IS STORE'
 ,'THIS IS NOT'
)