Project Botticelli

MDX Numeric Functions and CURRENTMEMBER Purchase this course

31 January 2014 · 5 comments · 5956 views

The Most Important Function in MDX

Chris Webb discuses the Sum() MDX function

There are a number of important functions in MDX that return numeric values. Chris Webb, the renowned authority on Multidimensional Expressions and all things related to cubes, explains these in this 30-minute, demo-driven tutorial.

We start with the Count() function, which takes a set and returns the number of items in the set. Even if this function looks similar to a SQL Count(), it cannot be used in an axis definition—you can only use the Count() function, and other numeric functions, inside a calculated member!

The Sum() function is similar, but it takes not only a set, but also a numeric expression. The numeric expression—usually a tuple—is evaluated for each item in the set, and then all of the values are summed up. Not all values in a cube should be summed up of course. For values such as distinct counts, for example, the Aggregate() function is a better choice. Like the Sum() function, it takes a set and a numeric expression, but unlike Sum() it looks at the AggregateFunction property of the measure values that you are working with, and for the measures that should be summed up, it sums values up, while for distinct counts it will do a distinct count of values over the set, and so on. The Aggregate() function should always be used when you are creating calculated members that return subtotal values. Other numeric values are also available, such as Min(), Max() and Avg().

The CurrentMember function is, generally speaking, the most important function in MDX: understanding how it works is the key to being able to write all kinds of really useful calculations in MDX, including time intelligence calculations. What it does is this: when you call it from inside a calculation, it tells you where the calculation is being executed inside the space of the cube. A calculation is executed inside a cell, and a cell has a tuple that points to it; the CurrentMember function allows you to inspect that tuple, and to find which member from any hierarchy on any dimension in the cube is present in that tuple. In most real-world calculations, you use the CurrentMember function to construct tuple or set expressions to find values elsewhere in the cube, relative to the cell you are currently in. Make sure to learn about CurrentMember, and the remaining numeric functions by watching Chris explain these concepts in his detailed, live demos, shown in the video.

Log in or purchase access below to the premium version of this content.

Purchase This Course or Full Access Subscription

Single Course

$250/once

Access this course for its lifetime*.
Purchase
Subscription

$480/year

Access all content on this site for 1 year.
Purchase
Group Purchase

from $480/year

For small business & enterprise.
Group Purchase

  • Redeem a prepaid code
  • Payment is instant and you will receive a tax invoice straight away.
  • Your satisfaction is paramount: we offer a no-quibble refund guarantee.

* We guarantee each course to be available for at least 2 years from today, unless marked above with a date as a Course Retiring.

Comments

russella0 · 10 December 2018

Hi Chris,

At time code 22:19 you create a calculated measure called DEMO that returns [DATE].[CALENDAR YEAR].[2001] that has no mention of another measure. I see this works and I understand what it's doing. What I don't understand is why this doesn't cause infinite recursion as we had earlier in the course when you create a calculated measure that doesn't move the context of the query to another measure.

Thanks,

Andy

Chris Webb · 10 December 2018

Hi Andy, I think the time you're referencing is wrong, but I think I know what you're talking about. DEMO is not a calculated measure - it's a calculated member on the Calendar Year hierarchy of the Date dimension. You only get infinite recursion on measures when you don't reference a measure in the calculation; in this case, when I create a calculated member on the Calendar Year hierarchy of the Date dimension, I don't get infinite recursion because I'm referencing another member on this hierarchy.

russella0 · 10 December 2018

Hi Chris,

Thank you for coming back to me.

The MDX that's on the screen at 22:34 is

WITH MEMBER MEASURES.DEMO
AS
[DATE].[CALENDAR YEAR].CURRENTMEMBER.NAME

So surely that is a measure ?

I appreciate this was 4 years ago for you now and that it's a bit above and beyond for me to expect direct support, it's more that MDX is finally opening up to me and I just can't get the penny to drop on this one :(

Best,

Andy

Chris Webb · 11 December 2018

Ahh yes, I understand what you're asking now. In this case there is no infinite recursion because the calculation just returns the name of the current member on the Calendar Year hierarchy. In the previous example the calculation uses a tuple to get a value from the cube, and when you are constructing a tuple to get data that's when you run the risk of infinite recursion because your tuple might refer to the cell you're currently calculating if you're not careful.

russella0 · 11 December 2018

Ah, yes, that makes sense now. Thank you for coming back to me.

Online Courses