CREATE member currentcube.measures.MyCellProperty AS <complex expression> , visible=0; CREATE member currentcube.measures.[Value] AS <exp> , backgroundColor=<MyCellProperty>; Eliminate Varying Attributes in Set Expressions Set expressions do not support . This restriction applies to all set functions including Filter, Aggregate, Avg, and others. You can work around this problem by explicitly overwriting invariant attributes to a single member. For example, in this calculation, the average of sales is computed, but only for sales exceeding $100. The query also runs very slowly ina recent desktop test on a fast server, it took about 55 seconds. – WITH member measures.AvgSales AS AVG( FILTER( descendants([Customer].[Customer Geography].[All Customers],,leaves) , [Measures].[Internet Sales Amount]>100 ) ,[Measures].[Internet Sales Amount] ) SELECT measures.AvgSales ON 0, [Customer].[Customer Geography].[City].members ON 1 FROM [Adventure Works] However, the average of sales for all customers everywhere should not depend on the current city. In other words, City should not be a varying attribute. You can eliminate City as a varying attribute and make the expression more efficient by using the All member as follows: WITH member measures.AvgSales AS AVG( FILTER( descendants([Customer].[Customer Geography].[All Customers],,leaves) , [Measures].[Internet Sales Amount]>100 ) ,[Measures].[Internet Sales Amount] ) member measures.AvgSalesWithOverWrite AS (measures.AvgSales, [All Customers]) SELECT measures.AvgSalesWithOverWrite ON 0, [Customer].[Customer Geography].[City].members ON 1 FROM [Adventure Works]
CREATE member currentcube.measures.MyCellProperty AS <complex expression> , visible=0; CREATE member currentcube.measures.[Value] AS <exp> , backgroundColor=<MyCellProperty>; Eliminate Varying Attributes in Set Expressions Set expressions do not support . This restriction applies to all set functions including Filter, Aggregate, Avg, and others. You can work around this problem by explicitly overwriting invariant attributes to a single member. For example, in this calculation, the average of sales is computed, but only for sales exceeding $100. The query also runs very slowly – ina recent desktop test on a fast server, it took about 55 seconds. WITH member measures.AvgSales AS AVG( FILTER( descendants([Customer].[Customer Geography].[All Customers],,leaves) , [Measures].[Internet Sales Amount]>100 ) ,[Measures].[Internet Sales Amount] ) SELECT measures.AvgSales ON 0, [Customer].[Customer Geography].[City].members ON 1 FROM [Adventure Works] However, the average of sales for all customers everywhere should not depend on the current city. In other words, City should not be a varying attribute. You can eliminate City as a varying attribute and make the expression more efficient by using the All member as follows: WITH member measures.AvgSales AS AVG( FILTER( descendants([Customer].[Customer Geography].[All Customers],,leaves) , [Measures].[Internet Sales Amount]>100 ) ,[Measures].[Internet Sales Amount] ) member measures.AvgSalesWithOverWrite AS (measures.AvgSales, [All Customers]) SELECT measures.AvgSalesWithOverWrite ON 0, [Customer].[Customer Geography].[City].members ON 1 FROM [Adventure Works]
Analysis%20Services%20MOLAP%20Performance%20Guide%20for%20SQL%20Server%202012%20and%202014.docx