CODEWITHSUNDEEP
mdx
user1508682
user1508682

Reputation: 1371

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?

Upvotes: 2

Views: 12765

Answers (3)

masnada001
masnada001

Reputation: 1

Based on the first answer, here is an approach that work for the all the measures in the cube :

SCOPE([Measures].MEMBERS);
  THIS = IIF(IsEmpty([Measures].CurrentMember) , 0, [Measures].CurrentMember);
END SCOPE;

hope it helps.

Upvotes: 0

SouravA
SouravA

Reputation: 5253

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;

Upvotes: 1

nsousa
nsousa

Reputation: 4544

Try re-defining your measures:

With member [Measures].[Not Null CTR] as Iif( IsEmpty( [Measures].[CTR] ), 0, [Measures].[CTR] )
...
Select { [Measures].[Not Null CTR], ...} on Columns
...

Perhaps changing the measure name for something nicer or renaming the columns back to the original names on the client.

EDIT: If you want to keep the names and the names output by your client are just CTR, etc. (without brackets or the Measures prefix), and you have an extra dimension available somewhere (one that is not used anywhere else in the query), you can define those new members in that extra dimension:

With member [My Other Dim].[CTR] as Iif( IsEmpty( [Measures].[CTR] ), 0, [Measures].[CTR] )
...
Select { [My Other Dim].[CTR], ...} on rows,
...

Definitely not an elegant solution, but it works.

Upvotes: 2

Related Questions