The combination of the filtering functionality built into PivotTables, and the ability to delete and reorder tuples in a set without needing to edit the set expression itself that the Excel named set functionality gives you, means that you can usually implement the filters you need in PowerPivot without needing to resort to MDX. However there are some scenarios where knowing the MDX functions that allow you to filter a set are useful and in this post I’ll show a few of them.

FILTER()

The Filter() function is the Swiss-Army penknife of filtering in MDX: it can do pretty much anything you want, but isn’t always the most elegant method. It’s quite simple in that it takes two parameters, the set that is to be filtered and a boolean expression that is evaluated for every item in the set and which determines whether that item passes through the filter or not.

Here’s a simple example. Consider a simple PivotTable (using my example model, described here) with FullDateAlternateKey on rows and the Sum of SalesAmount measure on columns:

The set of members on the FullDateAlternateKey level of the FullDateAlternateKey hierarchy can be obtained by using the .Members() function as I showed earlier in this series:
[DimDate].[FullDateAlternateKey].[FullDateAlternateKey].MEMBERS

This set can then be filtered by passing it to the Filter function, which itself returns a set, so it too can be used to create a named set. Let’s say we only wanted the set of dates where Sum of SalesAmount was greater than £10000; we could get it using the following expression:

Filter(
[DimDate].[FullDateAlternateKey].[FullDateAlternateKey].MEMBERS
, ([Measures].[Sum of SalesAmount])>10000)

What I’m doing is passing the set of all dates into the first parameter of Filter() and then, in the second parameter, testing to see if the value of the tuple ([Measures].[Sum of SalesAmount]) is greater than 10000 for each item in that set.

Here’s the result:

As I’ve mentioned before, it’s very important that you remember to check the ‘Recalculate set with every update’ button if you want the filter to be re-evaluated every time you change a slicer, which you almost always want to do.

Where is the filter function actually useful though? Here’s the same PivotTable but with Color on columns and with only Red and Black showing:

It’s not possible to filter this PivotTable to show only the dates where sales for Black products are greater than Sales of Red products using native functionality, but it is using MDX. Here’s the set expression:

Filter(
[DimDate].[FullDateAlternateKey].[FullDateAlternateKey].MEMBERS
, ([Measures].[Sum of SalesAmount], [DimProduct].[Color].&[Black])
>
([Measures].[Sum of SalesAmount], [DimProduct].[Color].&[Red])
)

Here I’m doing something similar to what I did in the first example, but now comparing two tuple values for each date: the tuple that returns the value of Sum of SalesAmount for Black products, which is
([Measures].[Sum of SalesAmount], [DimProduct].[Color].&[Black])
and the tuple that returns the value of Sum of SalesAmount for Red products, which is
([Measures].[Sum of SalesAmount], [DimProduct].[Color].&[Red])

NONEMPTY()

The NonEmpty() function also does filtering, but it’s much more specialised than the Filter() function – it filters items from a set that have empty values for one or more tuples. As with the Filter() function its first parameter is the set to be filtered, but its second parameter is another set, each of whose items are evaluated for each item in the first set. If one item in the second set evaluates to a non empty value for an item in the first set then that item passes through the filter.

That explanation is, I know, quite hard to digest so let’s look at an example. Here’s our PivotTable with no filter applied on rows and all Colors displayed on columns:

If you wanted to see only the rows where there were sales for Black products, you could use the following expression:

NonEmpty(
[DimDate].[FullDateAlternateKey].[FullDateAlternateKey].MEMBERS
, {([Measures].[Sum of SalesAmount], [DimProduct].[Color].&[Black])})

If you wanted to see only the rows where there were sales for Black OR Silver products, you could use this expression:

NonEmpty(
[DimDate].[FullDateAlternateKey].[FullDateAlternateKey].MEMBERS
, {([Measures].[Sum of SalesAmount], [DimProduct].[Color].&[Black])
,([Measures].[Sum of SalesAmount], [DimProduct].[Color].&[Silver])})

If you wanted to see only the rows where there were sales for Black AND Silver products you’d need to use two, nested NonEmpty functions:

NonEmpty(
NonEmpty(
[DimDate].[FullDateAlternateKey].[FullDateAlternateKey].MEMBERS
, {([Measures].[Sum of SalesAmount], [DimProduct].[Color].&[Black])})
, {([Measures].[Sum of SalesAmount], [DimProduct].[Color].&[Silver])})

TOPCOUNT(), BOTTOMCOUNT(), TOPPERCENT(), BOTTOMPERCENT()

The TopCount() and related functions are, as you’ve probably guessed from their names, useful for doing top N style filters. If you wanted to see the top 10 dates for sales of Black products you could use the following expression:

TopCount(
[DimDate].[FullDateAlternateKey].[FullDateAlternateKey].MEMBERS
, 10
, ([Measures].[Sum of SalesAmount], [DimProduct].[Color].&[Black])
)

Notice here how the dates are displayed in descending order for the Black column, but no other – that’s how you can tell that the TopCount() function is doing what you want.

To get the top N dates that provide at least 5% of the total sales across all time for Black products, you can use the following expression:

TopPercent(
[DimDate].[FullDateAlternateKey].[FullDateAlternateKey].MEMBERS
, 5
, ([Measures].[Sum of SalesAmount], [DimProduct].[Color].&[Black])
)

The BottomCount() and BottomPercent() function (I’m always reminded of one of the old, old jokes here when I use the BottomCount() function…) do the opposite and return the bottom items in a set, but you need to be careful using them because the bottom items in a set often have no values at all which is not very useful. So, for example, if you wanted to find the bottom 10 dates that have sales for Black products you need to use the NonEmpty() function as well as the BottomCount() function as follows:

BottomCount(
NonEmpty(
[DimDate].[FullDateAlternateKey].[FullDateAlternateKey].MEMBERS
, {([Measures].[Sum of SalesAmount], [DimProduct].[Color].&[Black])})
, 10
, ([Measures].[Sum of SalesAmount], [DimProduct].[Color].&[Black])
)

Here I’m taking the set of all members on the FullDateAlternateKey level of the FullDateAlternateKey hierarchy, passing that to the NonEmpty() function to return only the dates that have values for Black products and Sum of SalesAmount, and then getting the bottom 10 of those dates.

In part 5, I take a look at running MDX queries against a PowerPivot model.

In the first post in this series I looked at how the objects in a PowerPivot model mapped onto MDX objects; now, I’m going to start looking at some practical uses for MDX with Excel’s named sets functionality. Once you’ve created a PivotTable on top of your PowerPivot model, you can find this functionality on the PivotTable Tools/Options tab on the ribbon by clicking on the Fields, Items and Sets button:

What it does is allow you fine control over what appears on the rows and columns axes of your PivotTable. Now you don’t need to know any MDX to use this functionality to do fairly basic things like delete rows or to change the order, but with MDX you can do some pretty amazing things!

Consider the following simple PowerPivot model built from the Adventure Works DW database:

From this you can create a PivotTable with Calendar Years on rows and a measure that sums up the SalesAmount column:

With this PivotTable created, you can now go to the ribbon and you’ll see that there is the option to create a named set based on the selection you’ve made on rows:

Clicking on the Create Set Based on Row Items menu item opens the New Set dialog:

It’s here you can add, delete, copy and move rows; if you click the Edit MDX button then you can see the MDX representation of the selection you’ve made:

Here’s the actual MDX from this screenshot:

{([DimDate].[CalendarYear].&[2005]),([DimDate].[CalendarYear].&[2006]),([DimDate].[CalendarYear].&[2007]),([DimDate].[CalendarYear].&[2008]),([DimDate].[CalendarYear].[All])}

This is an MDX set expression: a set is just an ordered list of things, and there’s no equivalent object in DAX or SQL. Sets are written as comma delimited lists of members or tuples (we’ll come to what a tuple is later!) surrounded by curly brackets or braces, ie {}. This example is a set of Calendar Years in the following order: 2005, 2006, 2007, 2008 and a member called “All” which returns the Grand Total value. Sets can be given names – and so become named sets – and this means that anywhere MDX expects a set expression you can use the name of the set you’ve defined to return that set.

If, at this point, you click OK, a new named set called “Set1” will be created and that will be used as the selection on the rows axis of the PivotTable; since you haven’t changed any of the MDX, though, the PivotTable itself will look identical. The fact that the set is now used to control what’s on rows can be seen in the field list:

You can now go back and edit the set by clicking on the Manage Sets menu option under Fields, Items and Sets, selecting Set1 from the list of named sets and clicking Edit.

Rearranging and deleting items in the set expression in the Modify Set dialog that then appears (which looks the same as the New Set dialog above) changes the contents of the named set and so changes what is selected on the rows axis of the PivotTable. You can find the unique names of members and other objects by dragging them from the Fields, Items and Sets pane on the left hand side into the Set Definition text box on the right hand side.

It’s important, if you want the order of items in your set to be maintained (and you almost always do when writing your own MDX), that you also uncheck the Automatically Order and Remove Duplicates From the Set option at the bottom of the dialog:

For example, using the following set expression with automatic ordering turned on:

{([DimDate].[CalendarYear].&[2008]),
([DimDate].[CalendarYear].&[2005]),
([DimDate].[CalendarYear].&[2007])}

Gives you the set of years 2008, 2005 and 2007 in the order 2005, 2007 and 2008 and results in a PivotTable looking like this:

With automatic ordering turned off you get the set of years 2008, 2005, 2007 in that order, which is of course the order they are listed in the set:

MDX is a language plagued with brackets and commas and it’s very easy to make syntax errors when writing it. To check that your MDX is syntactically correct you can click the Test MDX button in the Modify Set dialog.

Now let’s talk about tuples. A tuple is another MDX concept that doesn’t have an equivalent in DAX or SQL; you can think of it as a kind of co-ordinate. Tuples are written as comma delimited lists of members surrounded by round brackets, ie (). As I said before, you can have sets of members or sets of tuples (well, strictly speaking all sets are sets of tuples but I won’t go there…) and the two sets you’ve seen above are sets of tuples. So the set:

{([DimDate].[CalendarYear].&[2008]),
([DimDate].[CalendarYear].&[2005]),
([DimDate].[CalendarYear].&[2007])}

…contains three tuples, and this:
([DimDate].[CalendarYear].&[2008])

…is a single tuple containing one member, the member for the year 2008 which has the unique name:
[DimDate].[CalendarYear].&[2008]

The set:

{[DimDate].[CalendarYear].&[2008],
[DimDate].[CalendarYear].&[2005],
[DimDate].[CalendarYear].&[2007]}

…is a set of three members – notice that the round brackets have disappeared – but will return the same three rows in a PivotTable as the previous set. In these two examples, each tuple or member (ie each item) in the set becomes a single row in the PivotTable.

Tuples can have more than one member in them though. Consider the following set:

{([DimDate].[CalendarYear].&[2008], [DimDate].[EnglishDayNameOfWeek].&[Monday]),
([DimDate].[CalendarYear].&[2005], [DimDate].[EnglishDayNameOfWeek].&[Friday]),
([DimDate].[CalendarYear].&[2007], [DimDate].[EnglishDayNameOfWeek].&[Tuesday])}

It still contains three tuples, but this time each tuple is composed of a year and a day name. Each tuple still becomes a row in the PivotTable (something which is best observed if you choose Show in Tabular Form on the PivotTable Design tab of the ribbon), but now each row has two levels of nesting, a year followed by a day name:

You can have as many members as you like in a tuple, so for example you could add some quarters too:

{([DimDate].[CalendarYear].&[2008],
[DimDate].[EnglishDayNameOfWeek].&[Monday],
[DimDate].[CalendarQuarter].&[1]),
([DimDate].[CalendarYear].&[2005],
[DimDate].[EnglishDayNameOfWeek].&[Friday],
[DimDate].[CalendarQuarter].&[4]),
([DimDate].[CalendarYear].&[2007],
[DimDate].[EnglishDayNameOfWeek].&[Tuesday],
[DimDate].[CalendarQuarter].&[1])}

I’ll finish off this post by mentioning the two important rules that you have to remember when defining sets and tuples, namely:

• Each item in a set has to be the same type of thing. So, if you have a set of members, each member has to come from the same hierarchy; if you have a set of tuples, each tuple has to contain the same number of members and each tuple has to contain members from the same hierarchy in the same position.
• Each item in a tuple has to be a member from a different hierarchy.

For example:

This is a valid set containing three members, because each member comes from the CalendarYear hierarchy:

{[DimDate].[CalendarYear].&[2008],
[DimDate].[CalendarYear].&[2005],
[DimDate].[CalendarYear].&[2007]}

This is not a valid set however, because it consists of two members from the CalendarYear hierarchy and one member from the CalendarQuarter hierarchy:

{[DimDate].[CalendarYear].&[2008],
[DimDate].[CalendarYear].&[2005],
[DimDate].[CalendarQuarter].&[1]}

This is a valid tuple because it contains three members from three different hierarchies:

([DimDate].[CalendarYear].&[2007],
[DimDate].[EnglishDayNameOfWeek].&[Tuesday],
[DimDate].[CalendarQuarter].&[1])

This is not a valid tuple because it contains two members from the CalendarYear hierarchy:

([DimDate].[CalendarYear].&[2007],
[DimDate].[CalendarYear].&[2005],
[DimDate].[CalendarQuarter].&[1])

This is a valid set of tuples because each of the three tuples consists of a member from the CalendarYear hierarchy followed by a member from the EnglishDayNameOfWeek hierarchy:

{([DimDate].[CalendarYear].&[2008], [DimDate].[EnglishDayNameOfWeek].&[Monday]),
([DimDate].[CalendarYear].&[2005], [DimDate].[EnglishDayNameOfWeek].&[Friday]),
([DimDate].[CalendarYear].&[2007], [DimDate].[EnglishDayNameOfWeek].&[Tuesday])}

Whereas this is not a valid set of tuples, even though each tuple on its own is valid, because the final tuple in the set contains a member from the EnglishDayNameOfWeek hierarchy followed by a member from the CalendarYear hierarchy, rather than a CalendarYear followed by an EnglishDayName of week:

{([DimDate].[CalendarYear].&[2008], [DimDate].[EnglishDayNameOfWeek].&[Monday]),
([DimDate].[CalendarYear].&[2005], [DimDate].[EnglishDayNameOfWeek].&[Friday]),
([DimDate].[EnglishDayNameOfWeek].&[Tuesday], [DimDate].[CalendarYear].&[2007])}

This is not a valid set of tuples either, because the final tuple (which again is valid in its own right) contains a CalendarYear followed by a CalendarQuarter:

{([DimDate].[CalendarYear].&[2008], [DimDate].[EnglishDayNameOfWeek].&[Monday]),
([DimDate].[CalendarYear].&[2005], [DimDate].[EnglishDayNameOfWeek].&[Friday]),
([DimDate].[CalendarYear].&[2007], [DimDate].[CalendarQuarter].&[1])}

Phew! I know the theory behind MDX can be very heavy going, but I promise you it’s important to learn it in order to be able to get the most out of the language. Next time, in part 3, I’ll look at some functions that returns sets and see how they can be used to construct more complex selections.