MDX - Count to return NULL instead of zero

Question

MDX - Count to return NULL instead of zero

Jonathan Mallia

SSCertifiable

Points: 5192
More actions
July 23, 2015 at 2:14 am

#306013

Hi everyone,
I have a Count measure that is simply counting over a set as follows:
MEMBER Measures.[Count Customers]
AS COUNT( EXISTING CurrentCustomers )
This query returns the correct result but is returning Zero for the months in the future..
For ex.
June-2015 5743
July-2015 7690
August-2015 0
September-2015 0
...
Is there a way to return NULL instead of zero, so that in the query when I specify NON EMPTY {} ON 1 will automatically not show future months?
Thanks!
Jon

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

PB_BI SSCoach Points: 17461 More actions · Answer 1

Jonathan Mallia (7/23/2015)
Hi everyone,
I have a Count measure that is simply counting over a set as follows:
MEMBER Measures.[Count Customers]
AS COUNT( EXISTING CurrentCustomers )
This query returns the correct result but is returning Zero for the months in the future..
For ex.
June-2015 5743
July-2015 7690
August-2015 0
September-2015 0
...
Is there a way to return NULL instead of zero, so that in the query when I specify NON EMPTY {} ON 1 will automatically not show future months?
Thanks!
Jon

This should be easy enough to achieve with an IIF block. Something like

IIF (COUNT( EXISTING CurrentCustomers ) = 0, NULL, COUNT( EXISTING CurrentCustomers ))

If that doesn't work can you post the full query and we can take it from there.

I'm on LinkedIn