MDX Error while creating Calculated Measure

Question

I am trying to create a calculated measure that finds the difference between two measures by using the following mdx query

WITH MEMBER [Measures].[Available]
 AS  ([Measures].[Capacity days] ,[Project].[Projects by Name].[All],[Project].[Projects by Code].[All]) 
    -  ([Measures].[Worked Days]  ,EXCEPT([Project].[Projects by Name].[Project].MEMBERS,
[Project].[Projects by Name].[Project].&[1214]),[Version].[Version].[Combined],[Charge].[Charge].[All])

In case of second measure Worked Days I want to access it with respect to all projects except one ,so am using EXCEPT function which results in the following error

" The function expects a string or numeric expression for the argument. A tuple set expression was used"

Is there any other way to perform this operation?

Marc Polizzi Marc Polizzi · Accepted Answer · 2014-08-14T07:32:18

The query is mixing tuples with sets. Perhaps you can check this gentle introduction of MDX for main concepts and notations.

The second tuple is using a set (the result of EXCEPT) as its second member which is not possible. You could use the aggregate function as following to compute the [Worked Days] over the members of this set instead :

AS    ( [Measures].[Capacity days], ... ) 
   -  Aggregate(
         Except ( 
           [Project].[Projects by Name].[Project].MEMBERS,
           [Project].[Projects by Name].[Project].&[1214] 
         ),
         ( [Measures].[Worked Days], ... )
      )

MDX Error while creating Calculated Measure

1 Answers