CODEWITHSUNDEEP
ssasmdx
Ivan Krivyakov
Ivan Krivyakov

Reputation: 2058

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?

Upvotes: 1

Views: 10147

Answers (2)

Ivan Krivyakov
Ivan Krivyakov

Reputation: 2058

Just retyping the resulting query in a more readable manner:

SELECT Measure1, Measure2, Measure3 ON COLUMNS
NonEmpty([Region].[Region].[Region], 
         { [Measure1], [Measure2] }) ON ROWS
WHERE -- some filter

If you don't use WHERE, you must be very careful to check what exactly your NonEmpty() runs on.

Upvotes: 0

daniel_aren
daniel_aren

Reputation: 1924

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/

Upvotes: 2

Related Questions