How do I use use an MDX set for a calculated member in Excel?

Question

I'm using Excel 2013 and SSAS 2014. I'm trying to group multiple members together for a report, based on the report context.

I'm trying to accomplish this by creating a custom member that contains all of the members I want to group. If I hard-code the member, it works fine, but using MDX, it fails.

This works:

[Terminals].[Terminal].[Terminal 1] + [Terminals].[Terminal].[Terminal 2]

This does not work:

Union([Terminals].[Terminal].[Terminal 1], [Terminals].[Terminal].[Terminal 2])

According to the documentation for the + operator:

The + (Union) operator is functionally equivalent to the Union (MDX) function.

However this must not be the case, since substituting the addition sign for the union function does not produce the same result. I cannot find an alternate form of the plus operator in the MDX reference.

Ultimately what I'm trying to do is filter members in a hierarchy by a data-defined rule:

Filter(
  [Terminals].[Terminal].Children,
  ClosingPeriod([Date].[By Week].[Date], [Measures].[Days])
)

The above function returns a set of Terminals where a record exists on the last day of the period selected in the query. The user may want to either INCLUDE or EXCLUDE these from the report.

How do I use use an MDX set for a calculated member in Excel?

UPDATE:

Greg's suggestion below works with Filter using the following syntax:

Aggregate(
    FILTER(
        NonEmpty([Terminals].[Terminal ID].Members),
        (ClosingPeriod([Date].[By Week].[Date]),[Measures].[Days]) = 1
    )
)

Note that Children didn't work, but Members did.

What do you mean by "does not work"? Is there an error message? — SouravA
It gives the all-obscure error message "The function expects a tuple set expression for the 1 argument. A string or numeric expression was used" — Robear

GregGalloway GregGalloway · Accepted Answer · 2015-11-25T03:49:31

If you are adding the calculated member to the Terminals dimension then this will work:

Aggregate(
 Union([Terminals].[Terminal].[Terminal 1], [Terminals].[Terminal].[Terminal 2])
)

If you are creating a new calculated measure do this:

Aggregate(
 Union([Terminals].[Terminal].[Terminal 1], [Terminals].[Terminal].[Terminal 2])
,[Measures].[Your Other Measure]
)

The same pattern can be applied to your more complex Filter() expression.

How do I use use an MDX set for a calculated member in Excel?

UPDATE:

1 Answers