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.