Search code examples
mdx

mdx - how to replace null values with '0' in measures members

In my MDX query I'm using this set of measures in my SELECT statement:

With SET [Selected Measures] AS { 
                                [Measures].[CTR],
                                [Measures].[Cost],                                  
                                [Measures].[Clicks]
                                }

I want in my result to replace the NULL values in '0'.

How to do this?

Solution

  • Is it possible to touch upon the cube design? If so you need the open the cube solution, navigate to the "Calculations" tab and add in the below code. Then deploy the changes.

    SCOPE([Measures].[CTR]);
IF THIS IS NULL THEN this = 0 END IF;
END SCOPE;

SCOPE([Measures].[Cost]);
IF THIS IS NULL THEN this = 0 END IF;
END SCOPE;

SCOPE([Measures].[Clicks]);
IF THIS IS NULL THEN this = 0 END IF;
END SCOPE;