Tecflix

MDX Numeric Functions and CURRENTMEMBER Purchase the entire course

31 January 2014 · · 6571 views

The Most Important Function in MDX

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 to play the video.

  • MDX Basic Concepts 29-min Watch with Free Subscription

  • MDX Queries 24-min Watch with Free Subscription

  • MDX Calculated Members 52-min

  • MDX: Member and Set Functions 38-min

  • What is the MDX Current Member Function? 5-min Free—Watch Now

  • MDX Numeric Functions and CURRENTMEMBER 33-min

  • MDX: Previous Period Growths, Shares & Moving Averages 38-min

  • More MDX Member & Set Functions 1-hour 37-min

  • What is a Subselect in MDX? 3-min Watch with Free Subscription

  • Exists, MDX Solve Order, and Subselects 49-min

  • MDX Performance Diagnosis with Profiler 29-min

Purchase a Full Access Subscription

 
Individual Subscription

$480/year

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

from $480/year

For small business & enterprise.
Group Purchase
 
  • You can also redeem a prepaid code.
  • Payments are instant and you will receive a tax invoice straight away.
  • We offer sales quotes/pro-forma invoices, and we accept purchase orders and bank transfers.
  • Your satisfaction is paramount: we offer a no-quibble refund guarantee.
  • See pricing FAQ for more detail.
In collaboration with
Project Botticelli logo Oxford Computer Training logo SQLBI logo Prodata logo