Update ALl measures using MDX Script in the calculations tab

Question

HI,

I updated all measures using MDX Script in the calculations tab
SCOPE([Measures].AllMembers);
THIS = CASE [Measures].AllMembers
WHEN [Measures].[MTD] THEN IIF ([Measures].[MTD] = 0 AND [Measures].[QTD] = 0 AND [Measures].[YTD] = 0, null, [Measures].[MTD])
WHEN [Measures].[QTD] THEN IIF ([Measures].[MTD] = 0 AND [Measures].[QTD] = 0 AND [Measures].[YTD] = 0, null, [Measures].[QTD])
WHEN [Measures].[yTD] THEN IIF ([Measures].[MTD] = 0 AND [Measures].[QTD] = 0 AND [Measures].[YTD] = 0, null, [Measures].[YTD])
ELSE IIF ([Measures].[MTD] = 0 AND [Measures].[QTD] = 0 AND [Measures].[YTD] = 0, null, [Measures].[MTD])
END;
END SCOPE

cube is updated and processed successfully. but while using the measures in the actual mdx it is giving following error message.
VALUE #Error MdxScript(RepCube) (3, 8) The CASE function expects a string or numeric expression for the 1 argument. A tuple set expression was used.
FORMATTED_VALUE #Error MdxScript(RepCube) (3, 8) The CASE function expects a string or numeric expression for the 1 argument. A tuple set expression was used.

can you please help me on this.

Thanks,
Narendra

Answer 1

The issue is the Measures.AllMembers set you are using in the CASE statement. As the error says, a CASE Statement needs a string or numeric value. But you could re-write this much more efficiently without a case statement using a series of direct assignments and one scope:

[Measures].[MTD] = IIF ([Measures].[MTD] = 0 AND [Measures].[QTD] = 0 AND [Measures].[YTD] = 0, null, [Measures].[MTD]);

[Measures].[QTD] = IIF ([Measures].[MTD] = 0 AND [Measures].[QTD] = 0 AND [Measures].[YTD] = 0, null, [Measures].[QTD]);

[Measures].[yTD] = IIF ([Measures].[MTD] = 0 AND [Measures].[QTD] = 0 AND [Measures].[YTD] = 0, null, [Measures].[YTD]);

// this applies to all measures except MTD, QTD and YTD
SCOPE ( Measures.AllMembers - {[Measures].[MTD],[Measures].[QTD],[Measures].[YTD]}) 
this = IIF ([Measures].[MTD] = 0 AND [Measures].[QTD] = 0 AND [Measures].[YTD] = 0, null, [Measures].[MTD]);
END SCOPE

However even then what you are trying to do does not make much sense to me. Where are these 0 values coming from? Can you avoid loading them into the cube in the first place and avoid having this extra logic?

Answer 2

I think Darren's post should be able to fix your issue ? If it helps you could accept it as answer.
If it not solved your problem completely you could share your further thoughts or issues , see if we could offer more help.