Thursday, May 8, 2014

MDX Calculated Member Check if Measure is Null

Josh,

So you want to create a count of a measure when it is not null.  To do so in MDX, you can do the following:

WITH Member [Measures].[Count of Non Null]
AS

IIF(ISEMPTY([Measures].[Might Be Null - Avg]), NULL, 1)

SELECT
   
NON EMPTY
   {
       [Measures].[Might Be Null - Avg],
       
[Measures].[Count of Non Null]
   } ON COLUMNS,
   
NON EMPTY
   {
       [Dim Date].[Fiscal Quarter].[Fiscal Quarter].ALLMEMBERS
   } ON ROWS
FROM
   
[MyCube]

No comments:

Post a Comment

Please only include comments that add to the substance of the Blog Post:
- Question or request for clarification
- Idea for improvement
- Identifying an issue

Please refrain from promotion.