In the cube in our project, there was a need to aggregate a measure only on 3 out of 6 dimensions, so that the aggregation would not filter this measure on any of the excluded 3 dimensions.
After trying Aggregate and Ancesters functions I could finally get correct results with the root function as explained in this blog
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/3b724376-aa6d-4bcc-afcb-a2ac07c94df5/mdx-to-aggregate-measure-over-specific-dimensions?forum=sqlanalysisservices
To sum it up, I created a calculated measure like:
member [measures].[TotalCustYearCat] as (
root([Date].[Calendar Year].currentmember),
root([Product].[Category].currentmember),
root([Customer]),
[Measures].[Internet Sales Amount]
)
After trying Aggregate and Ancesters functions I could finally get correct results with the root function as explained in this blog
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/3b724376-aa6d-4bcc-afcb-a2ac07c94df5/mdx-to-aggregate-measure-over-specific-dimensions?forum=sqlanalysisservices
To sum it up, I created a calculated measure like:
member [measures].[TotalCustYearCat] as (
root([Date].[Calendar Year].currentmember),
root([Product].[Category].currentmember),
root([Customer]),
[Measures].[Internet Sales Amount]
)
No comments:
Post a Comment