none
MDX Calculated measure - with dimension set RRS feed

  • Question

  • I'm trying to create a measure that has a set of dimension attributes

    This code returns #Error saying Aggregate cant be used on calculated members on the Measures dimension.

    WITH MEMBER [Measures].[EquipmentSpecificAvailability] AS  
    (
    AGGREGATE({[DIMEquipment].[ControlType].&[Item1] ,[DIMEquipment].[ControlType].&[Item4]}) 
    ,([DIMEquipment].[Class].&[ClassItem1]
    , [Measures].[Availability])
    )

    using cross join - the function expects a string or numeric expression for the argument - a tuple set was used

      MEMBER [Measures].[EquipmentSpecificAvailability] AS 
      ([Measures].[Availability])
    * {[DIMEquipment].[ControlType].&[Item1] ,[DIMEquipment].[ControlType].&[Item4]}  
    * {[DIMEquipment].[Class].&[ClassItem1] }

    I'm sure i'm making this harder than it needs to be but can't figure out the required syntax.

    Any assistance would be appreciated.

    Thursday, August 8, 2019 5:12 AM

All replies

  • The fix here is to simply use the appropriate aggregation function (ie. SUM, AVERAGE, MIN, MAX) that should be applied to the Availability measure instead of trying to use the generic AGGREGATE which only works on "raw" measures.

    http://darren.gosbell.com - please mark correct answers

    Thursday, August 8, 2019 6:03 AM
    Moderator
  • Hi Peter,

    The correct syntax should be like this.

    WITH MEMBER [Measures].[EquipmentSpecificAvailability] AS 
    AGGREGATE(
        {
    	[DIMEquipment].[ControlType].&[Item1],
    	[DIMEquipment].[ControlType].&[Item4]
    	}
    	*{DIMEquipment].[Class].&[ClassItem1]},
    	[Measures].[Availability]
    	)

    Reference

    Aggregate (MDX)

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, August 8, 2019 8:21 AM
  • Thanks Darren,

    I forgot a very key piece of information.

    If [Measures].[Availability] is itself a calculated measure, in this case, a percentage, is SUM still appropriate or is the solution Will posted below appropriate?

    I can answer this myself now. Aggregate does not work. However irrespective of the aggregation method used ie SUM, min, max - i get the same answer.

    I'm theorising that since the underlying measure is already a calculation, there is only 1 value returned so the aggregation type does not matter.

    It was just not intuitive to do it that way, so never tried.

    Regards

    Pete 


    • Edited by Peter Scurr Friday, August 9, 2019 12:29 AM
    Thursday, August 8, 2019 10:47 PM
  • Hi Pete,

    Which aggregation method it would use depends on the [Measures].[Availability] that defined in the cube.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, August 9, 2019 3:00 AM
  • The solution posted by WIll won't work either as you cannot use AGGREGATE inside a calculated measure, you can only use it inside a calculated MEMBER (ie. if you create the member on something other than [Measures] )

    You can really only SUM percentages if the percentages are created off a common denominator value, otherwise you would need to re-define the percentage calculation to operate how you want it to over the set of values.

    I'm not sure why sum, min, max would all produce the same value. That would imply either something wrong in your expression or the fact that one of Item1 or Item4 has not data against it for ClassItem1. I'd need to see the syntaxes you were trying to comment further.


    http://darren.gosbell.com - please mark correct answers

    Saturday, August 10, 2019 1:52 AM
    Moderator