MDX EXISTING keyword

Today I am going to talk about MDX "EXISTING" keyword. Seems the details provided on technet article is slightly complex to understand for a newbie developer hence I thought its better to share the details so that everyone can understand the basic usage of it.

Details: EXISTING keyword forces a specified SET to be evaluated within the current context.

Syntax: EXISTING Set_Expression

Example:

Consider a requirement wherein you want to COUNT the number of Products present under each Category. In such requirements you can build MDX using EXISTING keyword which actually explains the usage of keyword too. So we need to write a MDX query in following way for the requirement we have mentioned above.

WITH
  MEMBER [Measures].[X] AS
    COUNT([Product].[Product].[Product].MEMBERS)
  MEMBER [Measures].[Y] AS
    COUNT((EXISTING [Product].[Product].[Product].MEMBERS))
SELECT
  {
    [Measures].[X]
   ,[Measures].[Y]
  } ON 0
 ,[Product].[Category].[Category].MEMBERS ON 1
FROM [Adventure Works]

Output:

If you check the output, you will find that X is returning count of all products whereas Y is returning count of products belongs to each Category because we have given Product Category attribute on rows hence the set of products are evaluating under the context of Category.