MDX Calculated member filter by dimension attribute

Question

I want to create a calculated member and filter it by dimension. This is WORKING example:

(
    [Policy].[Policy Status].&[Void], 
    [Policy].[Tran Type].&[Renewal], 
    [Measures].[FK Policy Distinct Count]
)

But if I want to filter it like this

(
    [Policy].[Policy Status].&[Void], 
    [Policy].[Policy Status].&[Policy],  
    [Measures].[FK Policy Distinct Count]
)

Than it's NOT working. It says that same hierarchy is showing multiple times in the tuple.

Another thing is, how to exclude rows? Here's the idea...

(
    ![Policy].[Policy Status].&[Void], 
    ![Policy].[Policy Status].&[Policy],  
    [Measures].[FK Policy Distinct Count]
)

Answer 1

First, it's important to understand the MDX syntax, and how it related to the concepts of tuples, members and sets.

Tuples

Using parentheses denotes a tuple:

(
  [Policy].[Policy Status].&[Void], 
  [Policy].[Tran Type].&[Renewal], 
  [Measures].[FK Policy Distinct Count]
)

A tuple can only include a single member from any hierarchy.

Sets

To retrieve results from multiple members in the same hierarchy, you must query for a set. An MDX set is denoted by braces:

{
  [Policy].[Policy Status].&[Void], 
  [Policy].[Policy Status].&[Policy]
}

A set is, by definition,

an ordered collection of zero, one or more tuples.

So, if you wish to query for the [FK Policy Distinct Count] measure against both of those members, the set's tuples must each include the measure:

{
  ( [Policy].[Policy Status].&[Void],   [Measures].[FK Policy Distinct Count] ), 
  ( [Policy].[Policy Status].&[Policy], [Measures].[FK Policy Distinct Count] )
}

To simplify this expression, it is possible to crossjoin two sets of different dimensionality:

{
  [Policy].[Policy Status].&[Void],
  [Policy].[Policy Status].&[Policy], 
  [Policy].[Policy Status].&[Something], 
  [Policy].[Policy Status].&[Something else], 
  [Policy].[Policy Status].&[Yet another member]
}
*
{
  [Measures].[FK Policy Distinct Count]
}

Excluding rows

Now that we can define sets, it's time to remove some members from one. In your example, it sounds like you want to start with a level (which, to the MDX engine, is just a predefined set in the cube which includes every member at that level of the hierarchy), and exclude certain members. MDX has lots of functions that operate on sets, and we're going to use EXCEPT.

The EXCEPT function takes two parameters, the first being the set to remove from, and the second being the set which should be removed from the first. It returns a set.

In this example, I'm going to assume [Policy].[Policy Status] is an attribute hierarchy, and that its sole level has the Unique Name of [Policy].[Policy Status].[Policy Status].

EXCEPT(
  [Policy].[Policy Status].[Policy Status],
  {
    [Policy].[Policy Status].&[Void],
    [Policy].[Policy Status].&[Policy]
  }
)

This will return every member from the [Policy].[Policy Status].[Policy Status] level, except for [Policy].[Policy Status].&[Void] and [Policy].[Policy Status].&[Policy].

To get useful results, we can cross-join the result by a measure:

EXCEPT(
  [Policy].[Policy Status].[Policy Status],
  {
    [Policy].[Policy Status].&[Void],
    [Policy].[Policy Status].&[Policy]
  }
)
*
{
  [Measures].[FK Policy Distinct Count]
}

Using a set as a single member

Sets are nice, but sometimes all we want from them is to treat them as a single member, as in your calculated member requirement. To do this, we need to use an aggregation function. Aggregation functions take in a set and return a member that represents the entire set.

There are a number of these, and the right one to use depends on the data stored in your cube: MIN, MAX, COUNT, and SUM are some of them (see "Numeric Functions" in the MDX Function reference for a more complete list). In this example, I'll assume your dimension aggregates by using SUM:

SUM(
  EXCEPT(
    [Policy].[Policy Status].[Policy Status],
    {
      [Policy].[Policy Status].&[Void],
      [Policy].[Policy Status].&[Policy]
    }
  ),
  [Measures].[FK Policy Distinct Count]
)

Here, I have passed the measure to be aggregated as the second parameter to SUM.

---

MDX is a complex language which supports many common and uncommon set operations. If you haven't already, I advise taking the time to read over the documentation available online, or grab yourself a good MDX book. There's a lot to know :)

<3