Errors, MDX, SSAS

MDX Aggregate Functions Can Not Be Used on Calculated Members in the Measures Dimension

2 Comments

If you’ve ever tried to use the Aggregate function on a calculated measure, you’ve seen the following error:

Aggregate functions error

This is a problem if you’re trying to calculate something like Year To Date for a calculated measure. Take the following MDX query (which I understand doesn’t make sense and isn’t something you’d do in a real world situation, but just go with it for the sake of the example), which can be run against the Adventure Works cube, for example:

WITH MEMBER [Measures].[YTD Average Price] AS Aggregate ( PeriodsToDate( [Date].[Calendar].[Calendar Year], [Date].[Calendar].CurrentMember) , [Measures].[Internet Average Unit Price] ) Select [Measures].[YTD Average Price] on0, [Date].[Calendar].[Date] on1 From [Adventure Works] Where [Date].[Calendar Year].&[2003]

 

If you execute this query, it will complete successfully with #Error as the results. You have a couple different options. First, you could replace the Aggregate function with the Sum function. If you are unable to use the Sum function, then you’ll have to  adjust the way you calculate Year to Date Average Price. We’ll have to break this up into a couple different calculations.

WITH MEMBER [Measures].[YTD Unit Price] AS Aggregate ( PeriodsToDate( [Date].[Calendar].[Calendar Year], [Date].[Calendar].CurrentMember) , [Measures].[Internet Unit Price] ) MEMBER [Measures].[YTD Transaction Count] AS Aggregate ( PeriodsToDate( [Date].[Calendar].[Calendar Year], [Date].[Calendar].CurrentMember) , [Measures].[Internet Transaction Count] ) MEMBER [Measures].[YTD Average Price] AS [Measures].[YTD Unit Price]/[Measures].[YTD Transaction Count], format=“Currency“Select [Measures].[YTD Average Price] on0, [Date].[Calendar].[Date] on1 From [Adventure Works] Where [Date].[Calendar Year].&[2003]

 

Doing our calculation this way, we are able to work around our inability to use the Aggregate function on a calculated member. I hope this gives someone an idea of how they can work around this annoying issue.

2 thoughts on “MDX Aggregate Functions Can Not Be Used on Calculated Members in the Measures Dimension”

  1. Dustin – I plan on attending the free training tomorrow hosted by Pragmatic works. In anticipartion of that training – I have a bit of a technical question that I am hoping you can shed some light on. I have created a small SSAS cube from a small range of GL transaction data that basically is looking at parts sales by a heavy equipment company.

    So we have a number of salesman creating invoices with line items and part qty that show the sales and cost of sales for each transaction.

    The measures I am doing are sales, cost of sales, margin, – then Number of invoices, LineItems, and number of parts.

    All of these measure seem to be calculating fine except for the Invoices – which I need to have a distinct count on the Invoices. Along with the dollars meaures, the user wants to see number of invoices by salesman over time along with the count of line Items and the parts qty.

    So when I browse the DistinctCount on the Invoices is always coming up with the total number and it will not aggregate or slice over time or by salesman. No matter how I slice it it just keeps diplsying the grand total of invoices – like it is a constant number.

    I can show the distinct invoice counts by salesman/time levels doing TSQL using the fact and dimension tables – but not in the cube.

    So does this give you enough info to diagnose what I may be missing on the SASS side of the data?

    1. To me it sounds like there is not a relationship between the fact table and the dimensions you are trying to slice by. I would suggest checking the dimension usage tab and the DSV and making sure you have correctly related your dimensions to your facts.

Comments are closed.