More MDX Member & Set Functions Purchase the entire course

25 February 2014 · · 3635 views

Getting More Advanced

MDX has many set and member functions that make it easy to create all kinds of advanced common business calculations. Watch this full-length, 1-hour 40-minute, demo-rich video by Chris Webb to learn how to compute important business reporting metrics using MDX.

The PeriodsToDate() function, and related functions like YTD(), QTD() and MTD(), can be used to create to-date calculations. One example is a year-to-date calculation, which returns the sum of a measure value for all of the days in a year up to the current date. You can use the PeriodsToDate() function to do this, or if you have configured the Type property on your Date dimension you can use the more concise YTD() function.

The ParallelPeriod() function can be used to create same-period-previous-year calculations. It works as follows: you pass it a member, a level and an offset, and it finds the ancestor of the member at the given level, moves back the number of members specified in the offset and finally returns the member in the same relative position underneath the offset member, as the original member was underneath its ancestor.

There are a number of different ways to filter a set in MDX. The Filter() function is a general-purpose function that takes a set and a boolean expression, and returns only the items in the set where the boolean expression evaluates to True. NonEmpty() is a more specialised function that takes two sets, and returns only the items in the first set where there is at least one tuple in the second set that evaluates to a non-null value.

The TopCount() function takes a set, and it returns the top n values from that set by a given numeric expression; it’s useful for creating reports that show your top 10 products or sales people. There are a number of similar functions such as BottomCount(), TopPercent(), BottomPercent(), Head(), Tail(), plus the special Generate() function useful for nested TopCount() calculations.

There is no Order By clause in MDX—the items in a set can be ordered using the Order() function, and it can also be used with the Rank() function to create calculated measures that return ranks.

The StrToX family of functions take a string expression that contains MDX, evaluates the MDX, and returns the result. They must be used when working with parameterised MDX queries. They have an optional second parameter, the Constrained flag, that prevents MDX injection attacks. There is a related family of functions, the XToStr functions, that take MDX objects and return their string representations.

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


Access all content on this site for 1 year.
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