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:

image

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:

image

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:

image

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:

image

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:

image

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])})

image

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])})

image

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])})

image

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])
)

image

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])
)

image

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])
)

image

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.

17 thoughts on “Introduction to MDX for PowerPivot Users, Part 4: Filtering

  1. Hi Chris,

    Do you have any ideas about the following cubefunction filter problem with two conditions:

    I’ve managed to get the cube-function below working. It’s just an exampel, trying to return Revenue from days where the Revenue was greater than 1000. And it works

    =CUBEVALUE(“ThisWorkbookDataModel”;”[Measures].[Revenue]”;CUBESET(“ThisWorkbookDataModel”;”{FILTER([Dates].[DateKey].members,([Measures].[Revenue]100,[Measures].[Revenue]<1000))}"))

    Leaving the curly braces has no effect whatsoever. Do You have any ideas how to get this work?

  2. It seems that the start of the message disappeared. So the OK working version is: =CUBEVALUE(“ThisWorkbookDataModel”;”[Measures].[Revenue]”;CUBESET(“ThisWorkbookDataModel”;”{FILTER([Dates].[DateKey].members,([Measures].[Revenue]<1000))}"))

  3. I’m still puzzled with a bit different problem. If I use something else than measures as a condition in filter, Excel gives again N/A. Fex. if I want to just filter dates in year 2014:
    =CUBEVALUE(“ThisWorkbookDataModel”;”[Measures].[Revenue]”;CUBESET(“ThisWorkbookDataModel”;”{FILTER([Dates].[DateKey].members,[Dates].[YearKey]<2014)}"))
    It results in N/A

    • This doesn’t work because you’re assuming that [Dates].[YearKey] is something like a column in SQL. It’s not, its the name of a hierarchy and does not return a value that can be compared with anything else. What you want to do here is find the name (or the key) of the current member on that hierarchy, and then make the comparison. If you want the years in 2014, something like
      EXISTS([Dates].[DateKey].[DateKey].members,{[Dates].[YearKey].&[2014]})
      …is your best bet.

  4. Hi Chris! I stuck in MDX again. =( Is there any way to return a set with only one column after using GENERATE function?

    I have the following formula:

    Generate ( EXCEPT([Dates query].[Dates].[Year].MEMBERS, [Dates query].[Dates].UNKNOWNMEMBER),
    TopCount (
    {
    [Dates query].[Dates].CurrentMember*[MFC].[Products].[Company].Members

    },

    5,
    [Measures].[Share_new_Company]
    )
    )

    It always return 2 columns but I need just result of [MFC].[Products].[Company].Members.

    Any ideas?

    • How about

      Generate ( EXCEPT([Dates query].[Dates].[Year].MEMBERS, [Dates query].[Dates].UNKNOWNMEMBER),
      TopCount (
      {
      [MFC].[Products].[Company].Members

      },

      5,
      ([Measures].[Share_new_Company],[Dates query].[Dates].CurrentMember)
      )
      )

      • No, strangely it returns companies which weren’t in TOP 5 of any period, but not all companies. But it looks closer to solution I think.

      • It will return the correct companies, I promise, but it will union the results of the topcount() so you won’t see any duplicates. Also, if you’re using this in a query, remember you’ll need to display the dates somewhere otherwise you won’t see any meaningful measure values.

        Another idea would be to use your original expression and then pass the set to the Extract() function.

  5. Hello Chris

    Firstly thank you very much for this useful post. I have a question and hopefully if you have some time you can assist me in please?

    My objectives is to have a pivot table and a slicer with 2 selections of ‘top 3’ or ‘everything’.

    this is what i have now:

    iif([measures].[selectiontop]=”top3″,
    topcount([tabledata].[department].[department].members,
    3,
    ([measures].[series1],[tabledata].[department])),
    [tabledata].[department].members)

    Question: How do i go about getting the subtotal if the slicer selection if ‘top 3’? I currently have a subtotal of all department if the slicer selection if everything but not top 3.

    Thanking you for your time.

      • Yes Chris, i am still very new to all powerpivot, mdx and dax stuff.

        This is the formulas i used in the manage named sets section and i am using Excel 2013.

      • Unfortunately, with Excel 2013 there isn’t a way of doing this – you would need to be able to create calculated members to get the subtotals I think and this isn’t possible with Excel 2013 and Power Pivot.

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 )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s