## Archive for **November 2012**

## PASS Summit 2012 Schedule (and the first bit of juicy gossip!)

I’ve now arrived in Seattle for the PASS Summit 2012, and as always I’m looking forward to a busy week. There are a few things I wanted to highlight:

- There are always a few big announcements at PASS, and while I’ll be blogging every day you’ll hear the news first if you follow me on Twitter – I’m @Technitrain. For example I’ve just noticed a session on “Using Power View with Multidimensional Models” on the schedule which means, I suspect, that the long-promised support for Power View over SSAS Multidimensional, via DAX queries, will be announced this week!
- I’m doing a session on “What’s New in SSAS 2012?” (BIA-303) on Wednesday morning at 10:15am. Unfortunately, the printed schedule and summit guide booklet have the
**wrong**session title and/or abstract at the moment; the session details online are correct. This means that contrary to what you might see in some places I will not be doing a pure SSAS Multidimensional session; there will be about 15-20 minutes of 2012 Multidimensional content (I’d struggle to fill more than that) and the rest will cover terminology, Tabular, and choosing between Tabular and Multidimensional. - I’m doing a book signing with Marco and Alberto for our new book “SQL Server Analysis Services 2012: The BISM Tabular Model” on Thursday lunchtime from 12:00pm to 12:30pm at the Summit bookstore.
- I’m doing a second session on “The Best Microsoft BI Tools You’ve Never Heard Of!” (BI-202) on Thursday at 1:30pm. This will be a fun session showing how you can use tools like NodeXL and Layerscape alongside PowerPivot for BI purposes; and yes, I know, if you’re a regular reader of my blog you
*will*have heard of these tools… but hopefully you’ll find it enjoyable nonetheless.

If you see me wandering around at any point, feel free to say hello! I’m quite friendly and I won’t bite. I might be a bit jet-lagged though – I’m wide awake at 4:45am right now…

## Introduction to MDX for PowerPivot Users, Part 4: Filtering

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.