1. Filter() and IIF work funny in the columns hierarchy.<\/b>
\nConsider this:<\/p>\n
In this case the will return regions where market value is not empty, whereas<\/p>\n will return market value for all regions, empty or not, provided that the global market value is not empty.<\/p>\n To avoid this shenanigans one should filter on ROWS. E.g.<\/p>\n 2. Performance of FILTER on even medium sized sets is not so good.<\/b> If you rewrite the query above like this<\/p>\n you might get much better performance.SELECT
\nFILTER([MarketValue], [MarketValue]<\/b><\/font> >1000) ON COLUMNS,
\n[Region].[Region].[Region] ON ROWS
\nFROM [MyCube]<\/code><\/p>\nMarketValue<\/code><\/b><\/font> in red actually means the global<\/b> number for MarketValue, not the per region one. I.e., if the global MarketValue<\/code> is 1024, then all<\/i> regions will be returned. If global market value is 999, none of the regions will be returned. IIF<\/code> works in a similar way, and so does IsEmpty<\/code>. Thus,<\/p>\nSELECT NON EMPTY [MarketValue]<\/code><\/p>\nSELECT FILTER([MarketValue], NOT IsEmpty([MarketValue]))<\/code><\/p>\nSELECT
\n[MarketValue] ON COLUMNS,
\nFILTER( [Region].[Region].[Region], [MarketValue] > 1000) ON ROWS
\nFROM [MyCube]<\/code><\/p>\n
\nEven when the filter is trivial, something like 1=1, the query still takes several times longer than without the filter. If you filter on multiple fields, it makes situation even worse.<\/p>\nWTIH MEMBER [BigMarketValue] AS IIF([MarketValue]>1000, [MarketValue], NULL)
\nSELECT NON EMPTY [BigMarketValue] ON COLUMNS,
\n[Region].[Region].[Region] ON ROWS
\nFROM [MyCube]<\/code><\/p>\n