EXISTING FUNCTION giving incorrect result with subquery in MDX

Question

Using EXISTING function with subquery is giving incorrect result. I even tried in MS SMS in design mode and still the values are incorrect.

WITH SET [#DataSet#] as 'NonEmpty({[Store.State_Size].[All].Children})'   
SELECT {[Measures].[SALES_VOLUME], [Measures].[ATTRITION_SALES], [Measures].[NET_REVENUE]}   
on columns, NON EMPTY Hierarchize({[#DataSet#]})   
on rows FROM (   
SELECT ({ [Store.Store].[All].[Boston Region].[CT], [Store.Store].[All].[Boston Region].[MA], [Store.Store].[All].[Boston Region].[RI] })   
on columns FROM [Sales_Transactions] )   

Here [Measures].[ATTRITION_SALES] is using EXISTING function.

Answer 1

EXISTING doesn't react to subselects, although it does respect filtering introduced by WHERE. It's a long-known issue, and the solution is to author measures off dynamic sets which do react to subselects. You'll still need to add EXISTING to the dynamic set-based measures as appropriate.

Answer 2

Hi @Shaharyar Mahmood ,

Welcome to Microsoft Q&A!
Here are some tips you need to notice. Subselects have the following restrictions and limitations:
The WHERE clause does not filter the subspace.
The WHERE clause changes the default member in the sub cube only.
The NON EMPTY clause is not allowed in an axis clause; use a NonEmpty (MDX) function expression instead.
The HAVING clause is not allowed in an axis clause; use a Filter (MDX) function expression instead.
By default calculated members are not allowed in subselects; however, this restriction can be changed, in a per session basis, by assigning a value to the SubQueries connection string property in ConnectionString or DBPROP_MSMD_SUBQUERIES property in Supported XMLA Properties (XMLA). See Calculated Members in Subselects and Subcubes for a detailed explanation of the behavior of calculated members depending on the values of SubQueries or DBPROP_MSMD_SUBQUERIES.

For more details about Sub Select Vs Where Clause, please read this article: https://msbiworld.wordpress.com/2011/06/07/ssas-sub-select-vs-where-clause-3/
And there is a table at the end of the article to let you visualize the difference between the two.

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.