Category Archives: MDX

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.

MDX, MDX Puzzle Solution, SSAS

MDX Puzzles: Super 8–Solution

1 Comment

This week’s puzzle was pretty straight forward, but it required you to use the OpeningPeriod function. To show the count of customers on the first day of the year, we’ll create a calculated measure called “First Day Customer Count”.

WITH MEMBER [Measures].[First Day Customer Count] AS 
(OpeningPeriod([Date].[Calendar].[Date],[Date].[Calendar].CURRENTMEMBER),[Measures].[Customer Count])

In the above calculated measure, the first argument of the OpeningPeriod function specifies at which level we want the first period for and in this case we want the first period at the Date level. The second argument allows us to specify for which member we’d like to see the first day of the year.

Secondly, we’ll create a second calculated measure called “First Day”, which is very similar to our first calculated measure except we’ll be using the .NAME property to get the name of the first period of the specified level.

MEMBER [Measures].[First Day] AS
OpeningPeriod([Date].[Calendar].[Date],[Date].[Calendar].CURRENTMEMBER).NAME

Then finally we’ll reference these to calculated measures in our final select statement.

WITH 
MEMBER [Measures].[First Day Customer Count] AS 
(OpeningPeriod([Date].[Calendar].[Date],[Date].[Calendar].CURRENTMEMBER),[Measures].[Customer Count])

MEMBER [Measures].[First Day] AS 
OpeningPeriod([Date].[Calendar].[Date],[Date].[Calendar].CURRENTMEMBER).NAME

SELECT {[Measures].[First Day],[Measures].[First Day Customer Count]} ON 0,
[Date].[Calendar].[Calendar Year].MEMBERS ON 1

FROM [Adventure Works]

I’d like to say thanks to everyone who participated in this MDX puzzle. We had several great submissions but I can only pick one winner. Super 8’s puzzle winner is Jason Thomas (@de_unparagoned)! Congrats, Jason! I’ll be sending you one cool Kick’n SSAS t shirt in the mail as soon as I get off the road and back home next week.

Stay tuned for the next round of MDX Puzzles!

MDX, SSAS

MDX Puzzles: Super 8

1 Comment

If you’ve followed my blog over on BIDN.com, you’ve probably come across my series of MDX puzzles. If you’re not familiar with the MDX Puzzles series of post, check this out. As always, these MDX queries were written against the Adventure Works DW. Here are the requirements:

Rows:

  • Calendar Year

Columns:

  • Customer Count on the first day of the year (may not be Jan 1 if Jan 1 does not exist)
  • First Date in Year

Hints:

  • Take a gander at this

And here’s a sneak peak at my results:

QueryResults

If you think you’ve got the correct answer, send your query to

 email me

Of all the correct answers I get, I’ll pick one at the end of next week (April 29) and send out one of these really cool shirts, so good luck, peeps!

KickinSSAStshirts