Handling Missing Members In The CubeSet() Function With Power Pivot

Last week I received an email from a reader asking how to handle missing members in MDX used in the Excel CubeSet() function. My first thought was that this could be solved easily with the MDXMissingMemberMode connection string property but it turns out this can’t be used with Power Pivot in Excel 2013/6 because you can’t edit the connection string back to the Excel Data Model:

image

Instead, you have no choice but to handle this in MDX.

Here’s an illustration of the problem. Imagine you have the following table of data on your Excel worksheet:

image

With this table added to the Excel Data Model, you could write the following Excel formula using CubeSet():

=CUBESET(
	"ThisWorkbookDataModel",
	"{[Sales].[Product].[All].[Apples], 
	  [Sales].[Product].[All].[Oranges], 
	  [Sales].[Product].[All].[Pears]}", 
	"Set")

image

In the screenshot above the CubeSet() formula is used in H3, while in H4 there’s a formula using CubeSetCount() that shows the set contains three members.

If the source data is updated so that the row for Pears is deleted like so:

image

Then the CubeSet() formula returns an error because the member Pears no longer exists:

image

How can this be avoided? If what you actually wanted was all of the Products, whatever they were, the best thing to do is to use the MDX Members function like so:

=CUBESET(
	"ThisWorkbookDataModel",
	"{[Sales].[Product].[Product].MEMBERS}", 
	"Set")

[I talk about the Members function in this post in my series of posts on MDX for Power Pivot users]

This formula does not return an error and you can see that the CubeSetCount() formula below shows the set only contains two members now:

image

If you do need to refer to individual members then the MDX you need is more complicated:

=CUBESET(
	"ThisWorkbookDataModel", 
	"{[Sales].[Product].[All].[Apples], 
	 [Sales].[Product].[All].[Oranges], 
	 iif(
		iserror(
		 strtomember(""[Sales].[Product].[All].[Pears]"")
		), 
		{},
		{strtomember(""[Sales].[Product].[All].[Pears]"")} 
	 )
	}", 
	"Set")

image

This MDX uses the StrToMember() function to interpret the contents of a string as an MDX expression returning a member; if this expression returns an error then it is trapped by the IsError() function and an empty set is returned.

This isn’t particularly pretty, though, and ideally the MDXMissingMemberMode connection string property would be set to Ignore in the Excel Data Model connection string property.

8 thoughts on “Handling Missing Members In The CubeSet() Function With Power Pivot

  1. chris

    thanks for writing about cube formula, I tried a lot Fancy charts in self service BI tool, but nothing beat a cube formula in Excel, this thing is so powerfull

  2. Chris,

    Would you please write a book on CUBE formulas? I have been looking but cannot find a good one. They are so powerful and much more flexible than Pivot Tables.

  3. Hi Chris,

    I converted the following DAX measure into a MDX dynamic named set and calculated member:

    Earliest Selected Year Sales:=
    VAR
    EarliestSelectedYear = CALCULATE(MIN(‘Order Date'[Fiscal Year]), ALLSELECTED(‘Order Date'[Fiscal Year]))
    RETURN
    CALCULATE([Sales],’Order Date'[Fiscal Year] = EarliestSelectedYear)

    CREATE HIDDEN DYNAMIC SET CURRENTCUBE.[Hidden Dynamic Set] AS
    HEAD([Order Date].[Fiscal Year].[Fiscal Year].MEMBERS);

    CREATE MEMBER CURRENTCUBE.[Measures].[Earliest Selected Year Sales] AS
    AGGREGATE(
    [Hidden Dynamic Set]
    ,[Measures].[Sales]
    ),
    FORMAT_STRING = “$#,##0.00;($#,##0.00)”,
    VISIBLE = 1;

    Is it possible to prevent the dynamic named set from being recomputed every time a query is run? How would you write the MDX measure?

    Thank you,

    John

    • No, unfortunately – by definition a dynamic named set has to be evaluated each time a query is run. Presumably you’re handling a situation where a user can select multiple years? If so, your current MDX is probably the best way of doing this.

  4. Thanks, Chris.

    My goal was to create a group of measures that compare annual totals against a single base year rather than using the more common year-over-year comparison. For example, if the user selects FY15, FY16, FY17, and FY18, then the following comparisons would be made:
    FY15 vs FY15
    FY15 vs FY16
    FY15 vs FY17
    FY15 vs FY18

    A hypothetical example (which might be different in your experience) is technology book sales where the initial release period generates the most sales and subsequent periods show a steady decline over time. If the initial release period is the most important period (the bellwether), then it might be relevant to compare all subsequent periods to the initial release period.

    I read about the dynamic named set approach on Gerhard Brueckl’s blog: https://blog.gbrueckl.at/2014/12/ssas-dynamic-named-sets-in-calculated-members/

    Gerhard wrote that a dynamic named set is recomputed every time a query is executed, even if the dynamic named set is not used in a query. I was hoping that you might know either a better approach or a workaround to prevent the dynamic named set from being recomputed when not referenced in a query.

    Thank you,

    John

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s