Replacing blank with "NA" or "MISSING" text

Question

I am trying to replace blank spaces in data with the "MISSING" or "NA" text.

CoalesceEmpty([Product Type].[All Product Type].[                  ],"MISSING")

I have tried the above code, with or without quotes around MISSING.

Getting the following error: CoalesceEmpty([Product Type].[All Product Type].[ ],"MISSING") is invalid and cannot be used in a query.

P.S. Product Type is a dimension.

I tried this function on measures and it worked fine there.

score 0 · Answer 1 · answered Mar 11 '15 at 07:44

0

CoalesceEmpty function doesn't accept members or sets. Both the parameters need to be numeric expressions. See the msdn link here

answered Mar 11 '15 at 07:44

SouravA

5,147
2
24
49

It does accept , strings right? as i can see String syntax CoalesceEmpty(String_Expression1 [ ,String_Expression2,...n] ) – in_user Mar 11 '15 at 07:47
Yes it does but your first parameter ain't a string mate! It's a member. – SouravA Mar 11 '15 at 07:53

whytheq · Accepted Answer · 2015-03-15T21:56:06.850

I don't understand what this gap means - do you really have a member that is called [ ]?! ... [Product Type].[All Product Type].[ ]

We also need to see the whole script - the context for this snippet - to accurately help you.

If you wanted to test the currentmember of the hierarchy [Product Type].[All Product Type] for null then you could do the following:

IIF(
  [Product Type].[All Product Type].currentmember.membervalue = 0,
  "MISSING",
  [Product Type].[All Product Type].currentmember.membervalue
)

In the cubes I play with the above would never happen as all members exist so all members have a membervalue. Here is an example of replacing blanks:

WITH 
  MEMBER [Measures].[Internet Sales Amount 2] AS 
    IIF
    (
      [Measures].[Internet Sales Amount] = 0
     ,'MISSING'
     ,[Measures].[Internet Sales Amount]
    ) 
SELECT 
  {
    [Measures].[Internet Sales Amount]
   ,[Measures].[Internet Sales Amount 2]
  } ON 0
 ,
    [Customer].[Customer Geography].[Country].MEMBERS
  * 
    [Product].[Category].MEMBERS ON 1
FROM [Adventure Works];

The new measure I've created results in the 4 column below:

enter image description here

Replacing blank with "NA" or "MISSING" text

2 Answers2