SSAS MDX : aggregate functions cannot be used on calculated measures in the measures dimension

in one of my reauirement we need to create a measure  with fallowing condition
Application = S6A/S6D
Command = ULR
Origin Realm <> epc.mnc001.mcc206.3gppnetwork.org *
Destination Realm = epc.mnc001.mcc206.3gppnetwork.org

Then we created calculated measure

WITH
MEMBER
NewMember
AS

AGGREGATE
(
(
 
[D Application].[Application].&[S6a/S6d]
 
,[D Command].[Command].&[ULR]
 
,{Except([D Origin Realm].[Origin Realm],[D Origin Realm].[Origin Realm].&[epc.mnc001.mcc206.3gppnetwork.org])}
 
,[D Destination Realm].[Destination Realm].&[epc.mnc001.mcc206.3gppnetwork.org]
)
,[Measures].[SuccessRate]
)

SELECT
[Measures].[NewMember] ON 0,
non empty
([D Date].[Hierarchy].members) on 1
from [DRV]
then we got the error
aggregate functions cannot be used on calculated measures in the measures dimension

The reason for this error is SuccessRate is Calculated measure and we are using that in Aggregate function . To Avoid that we used  a necessary static operator, i.e. SUM, COUNT etc.

CREATE SET CURRENTCUBE.[Home Origin]
 AS (
  [D Application].[Application].&[S6a/S6d]
  ,[D Command].[Command].&[ULR]
        ,{Except([D Origin Realm].[Origin Realm],[D Origin Realm].[Origin Realm].&[epc.mnc001.mcc206.3gppnetwork.org])}
  ,[D Destination Realm].[Destination Realm].&[epc.mnc001.mcc206.3gppnetwork.org]
 );  

CREATE MEMBER CURRENTCUBE.[Measures].[Roaming Out LU SR]
 AS IIF([Measures].[SuccessRate] > 0
,sum
(
 [Home Origin]
 ,[Measures].[SuccessRate]
)
,null)
,
FORMAT_STRING = "Standard",
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'F DRA Rate Count'  ;

Comments

Popular posts from this blog

SSAS : A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. (Microsoft.AnalysisServices.AdomdClient). + Solution

SSIS Error : Unable to infer the XSD from the XML file. The XML contains multiple namespaces