2

I have a very simple Calculated Member:

CREATE MEMBER CURRENTCUBE.[Account].[Account Parent].&[NA_1].[Beginning Subscribers]
  AS -[Account].[Account Parent].&[Beginning_Subscribers]

However, the Parent doesn't seem to calculate or simply ignores the Calculated member in Excel. See below:

Unwanted Image

It should look like this:

Wanted Image

Account Dimension

This is the MDX on the Excel side:

Select /* Grid - 'Sheet1'!$A$1 */ Non Empty {[Calendar Month].[Calendar Month Parent].&[2018-11],[Calendar Month].[Calendar Month Parent].&[2018-12]} On Columns, Non Empty AddCalculatedMembers ( DrillDownMember ( AddCalculatedMembers ( {Descendants([Account].[Account Parent].&[Net_Mig_1],[Account].[Account Parent].&[Net_Mig_1].Level,AFTER)} ) , { [Account].[Account Parent].&[Net_Mig_1],[Account].[Account Parent].&[NA_1] } , Recursive ) ) On Rows From [TEST CALCS] Cell Properties VALUE
Rob Allison
  • 21
  • 3
  • Please provide mdx, produced by excel, and Account dimension data. – Alex Sham Mar 01 '19 at 22:50
  • Select Non Empty {[Calendar Month].[Calendar Month Parent].&[2018-11],[Calendar Month].[Calendar Month Parent].&[2018-12]} On Columns, Non Empty AddCalculatedMembers ( Hierarchize ( DrillDownMember ( {[Account].[Account Parent].&[Net_Mig_1]} , { [Account].[Account Parent].[All],[Account].[Account Parent].&[Rollup Account],[Account].[Account Parent].&[Subsribers],[Account].[Account Parent].&[Net_Mig_1],[Account].[Account Parent].&[NA_1] } , Recursive ) , Post ) ) On Rows From [TEST CALCS] Cell Properties VALUE – Rob Allison Mar 02 '19 at 00:09
  • This is more correct. I had edited it a few times since the original post. Select /* Grid - 'Sheet1'!$A$1 */ Non Empty {[Calendar Month].[Calendar Month Parent].&[2018-11],[Calendar Month].[Calendar Month Parent].&[2018-12]} On Columns, Non Empty AddCalculatedMembers ( DrillDownMember ( AddCalculatedMembers ( {Descendants([Account].[Account Parent].&[Net_Mig_1],[Account].[Account Parent].&[Net_Mig_1].Level,AFTER)} ) , { [Account].[Account Parent].&[Net_Mig_1],[Account].[Account Parent].&[NA_1] } , Recursive ) ) On Rows From [TEST CALCS] Cell Properties VALUE – Rob Allison Mar 02 '19 at 00:33
  • @Alex Sham As far as the Dimension data, I think this is what you are asking for. It is a Parent Child Hierarchy. Child Parent Net_Mig_1 Subscribers NA_1 Net_Mig_1 Acquisitions NA_1 Ending_Subscribers NA_1 Gross_Additions Net_Mig_1 Net_Deactivations Net_Mig_1 – Rob Allison Mar 02 '19 at 00:35
  • Speaking about your mdx: I’m not able to understand it, why are there drilldown, descendant functions, if you use simple member*-1?Where are measures?Account seems to be a dimension.Provide full structure,pls. Can you write simple mdx to check your formula for member? WITH MEMBER my_member as IIF([Account].[Account Parent].currentmember is [Account].[Account Parent].&[Beginning_Subscribers],-1, 1) * your measure select my_member on columns, Non empty [Account].[Account Parent].&[NA_1].Children on rows from [TEST CALCS] WHERE ([Calendar Month].[Calendar Month Parent].&[2018-11])? – Alex Sham Mar 03 '19 at 17:53
  • I am new to MDX and we are using an Excel addin called XLCubed to view the data in our cube. I copied that code from XLCubed. Account is a dimension and Beginning Subscribers is a Calculated Member I am trying to add to the existing Account Dimension and include it in the NA_1 level of that Dimensions Hierarchy. There is only one Measure in the cube called Value. – Rob Allison Mar 04 '19 at 18:10
  • I ran your code above and it came back with "my_member" and no data so it looks like the formulas do the same thing. – Rob Allison Mar 04 '19 at 20:41

0 Answers0