Search code examples
ssasmdx

MDX: efficient way to filter tuples where particular columns are not empty?

Suppose I have an MDX query like this: 

SELECT Measure1, Measure2, Measure3 ON COLUMNS
[Region].[Region].[Region] ON ROWS
FROM TheCube

If I wanted to exclude rows where ALL THREE measures are empty, I would use SELECT NON EMPTY, which works fast. But I actually need to exclude rows where both Measure1 and Measure2 are empty, even if Measure3 has a value - because in this particular cube Measure3 always has a value, so NON EMPTY has no effect at all.

I could do 

SELECT Measure1, Measure2, Measure3 ON COLUMNS
FILTER ([Region].[Region].[Region], 
       NOT (IsEmpty(Measure1) AND IsEmpty(Measure2)) ON ROWS
FROM TheCube

and it even works, but it takes forever: an order of magnitude longer than the NON EMPTY query above. In fact, even if I filter by an expression that is always true, like FILTER(..., 1=1), it also takes a lot of time.

Is there a more efficient way to filter out rows where both Measure1 and Measure2 are empty?

Solution

  • I think you are looking for the similar function NonEmpty. http://msdn.microsoft.com/en-us/library/ms145988.aspx Here is a good explanation between them: http://thatmsftbiguy.com/nonemptymdx/