1. Filter() and IIF work funny in the columns hierarchy.
Consider this:
SELECT
FILTER([MarketValue], [MarketValue] >1000) ON COLUMNS,
[Region].[Region].[Region] ON ROWS
FROM [MyCube]
In this case the MarketValue
in red actually means the global number for MarketValue, not the per region one. I.e., if the global MarketValue
is 1024, then all regions will be returned. If global market value is 999, none of the regions will be returned. IIF
works in a similar way, and so does IsEmpty
. Thus,
SELECT NON EMPTY [MarketValue]
will return regions where market value is not empty, whereas
SELECT FILTER([MarketValue], NOT IsEmpty([MarketValue]))
will return market value for all regions, empty or not, provided that the global market value is not empty.
To avoid this shenanigans one should filter on ROWS. E.g.
SELECT
[MarketValue] ON COLUMNS,
FILTER( [Region].[Region].[Region], [MarketValue] > 1000) ON ROWS
FROM [MyCube]
2. Performance of FILTER on even medium sized sets is not so good.
Even 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.
If you rewrite the query above like this
WTIH MEMBER [BigMarketValue] AS IIF([MarketValue]>1000, [MarketValue], NULL)
SELECT NON EMPTY [BigMarketValue] ON COLUMNS,
[Region].[Region].[Region] ON ROWS
FROM [MyCube]
you might get much better performance.