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:


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:


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



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:


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


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:


[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:


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



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 responses

  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. Pingback: Dew Drop - February 20, 2017 (#2425) - Morning Dew

  3. 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.

  4. Hi Chris,

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

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

    HEAD([Order Date].[Fiscal Year].[Fiscal Year].MEMBERS);

    CREATE MEMBER CURRENTCUBE.[Measures].[Earliest Selected Year Sales] AS
    [Hidden Dynamic Set]
    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,


    • 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.

  5. 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,


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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: