MDX Error: referring the calculated measure (using with member) within a subcube return an error

Question

I'm pretty new to MDX. Can you please help me out in clearing this concept. Your help is appreciated.

I've the below MDX query which executes perfectly fine.

with member [measures].[perc] as
([Dim Customer].[Customer ID].currentmember,[Measures].[Amount])

select
[measures].[perc] on 0,
[Dim Customer].[Customer ID].children on 1
from [Analysis DW]

But, when I try to refer [measure].[perc], I get an error saying "The '[perc]' member was not found in the cube when the string, [measures].[perc], was parsed."

Please find below the error mdx script for reference-

with 
 member [measures].[perc] as
([Dim Customer].[Customer ID].currentmember,[Measures].[Amount])

select [measures].[perc]  on 0
from
    (
       select
       [measures].[perc] on 0,
       [Dim Customer].[Customer ID].children on 1
        from [Analysis DW]
     )

SouravA SouravA · Accepted Answer · 2014-10-13T13:10:21

Coming a lot late, but thought this could benefit someone with a similar problem.

Your MDX query fails because the calculated measure is out of context for the subcube. You are declaring [Measures].[perc] above the MDX select snippet. So, by design, it is visible to the select following it. But it is not visible to the subcube inside the Select. Thus the following piece of code makes no sense at all to the SSAS engine.

   select
   [measures].[perc] on 0, //Compiler is like .... WHAT THE %@#^ ??
   [Dim Customer].[Customer ID].children on 1
   from [Analysis DW]

It is as good as writing

   select
   [measures].[RandomGarbageName] on 0, 
   [Dim Customer].[Customer ID].children on 1
   from [Analysis DW]

Hope this helps.

MDX Error: referring the calculated measure (using with member) within a subcube return an error

2 Answers