About based filtering

Jul 31, 2012 at 2:28 PM

Hi,

thanks for your work, which I will suggest to my customer, a former user of ProClarity.

What do you think about further extending the Pivot Table filter function to support filters based on a certain set, like show only the customers with a sales amount > 100,000 based on sales of 2009?

This is possible in ProClarity but not in Excel OLAP Pivot Table and would be another step to enhance filtering in Excel BI.

Looking forward to your comment.

Helmut

Coordinator
Jul 31, 2012 at 11:16 PM

If you put Customers on rows, then right-click and choose Filter... Value filters... then you can choose to filter to only rows where Your Sales Measure > 100000. That particular approach won't perform very well until you upgrade to AS2012 SP1, but it works.

If you don't have your entire PivotTable filtered to 2009, but you want to filter based upon 2009 sales, then you could use my add-in to create a [Measures].[2009 Sales] measure and then use it in the Value Filters.

Probably the best approach is to use Excel 2010 to create a named set and specify the following MDX:

NonEmpty(
 [Customer].[Customer].[Customer].Members
 ,IIf(
  ([Measures].[Sales],[Date].[Year].[2009]) > 100000
  ,1
  ,null
 )
)

That MDX returns the customers with more than 100000 in sales in 2009. If you put that on rows, it will perform well and work well.

Do you think that covers your needs pretty well?

Aug 1, 2012 at 2:17 PM

Hi furmangg,

thank you, this may be a valid solution, especially when the performance is OK - will discuss it with my customer.

Cheers, Helmut

Aug 3, 2012 at 1:12 PM

In Excel 2010 the MDX above gives

The NONEMPTY function expects a tuple set expression for the 2 argument. Astring or numberic expression was used.

Therefore I did replace it by

Filter(
[Customer].[Customer].[Customer].Members
 ,([Measures].[Sales],[Date].[Year].[2009]) > 100000
)

Coordinator
Aug 3, 2012 at 4:01 PM

Oh dear. You're right. That MDX was off the top of my head and I made a mistake.

Unfortunately, the Filter function won't perform very well until AS2012 SP1. If performance isn't adequate for you, then add the following [Measures].[2009 Sales] calculated measure, then uncheck it to remove it from the PivotTable.

IIf(
 ([Measures].[Sales],[Date].[Year].[2009]) > 100000
 ,1
 ,null
)

Then your named set expression can be:

NonEmpty(
 [Customer].[Customer].[Customer].Members
 ,[Measures].[2009 Sales]
)

That should perform better.

Marked as answer by furmangg on 6/9/2014 at 12:54 AM