MultiDimensional Expressions (MDX) is one of the most powerful features available in IBM Planning Analytics Workspace (PAW). While many TM1 developers rely on static subsets, mastering MDX opens the door to dynamic reports, intelligent dashboards, and significantly reduced maintenance
In this first article of the series, we'll explore three fundamental MDX functions that every TM1 developer should understand:
TopCount()
Filter()
Descendants()
These functions form the building blocks of many real-world PAW applications.
Static subsets require manual maintenance and often become outdated. MDX allows subsets to adapt automatically to changing data and hierarchies.
With MDX, you can:
Create dynamic reports.
Build interactive dashboards.
Automatically rank and filter data.
Simplify maintenance.
Deliver more flexible analytics to business users.
Let's look at three commonly used functions.
The TopCount() function returns the top members from a set based on a specified measure.
Syntax
Example: Top 10 Products by Revenue
What This Does
Evaluates all products.
Sorts them based on Revenue.
Returns the top ten products.
Typical Use Cases
Top 10 customers by sales.
Highest-cost departments.
Top-performing regions.
Best-selling products.
Dashboard Example
A PAW dashboard displaying:
Top 10 Products by Revenue
Since the subset is dynamic, rankings update automatically whenever the data changes.
The Filter() function allows you to return members that satisfy a specific condition.
Syntax
Example: Customers with Revenue Above $1 Million
What This Does
Evaluates every customer.
Keeps only customers whose revenue exceeds $1 million.
Excludes all others
Budget Variance Reporting
Show only cost centers exceeding budget:
Active Employees
Display only employees with a headcount value greater than zero:
Product Profitability
Show products with positive margins:
Why Use Filter?
Instead of maintaining manual subsets every month, your reports automatically update based on business rules.
TM1 dimensions are hierarchical by nature. The Descendants() function retrieves all child members below a specified parent.
Syntax
Example
Suppose the Region hierarchy looks like this:
Using:
returns:
Regional Reporting
Display all locations under North America.
Cost Centre Rollups
Retrieve all departments under Finance.
Organizational Structures
Expand reporting relationships automatically.
Product Hierarchies
Display all SKUs belonging to a product family.
Benefits
When new members are added to the hierarchy, they are automatically included without requiring changes to the subset.
MDX functions become especially powerful when combined.
Example: Top 10 Profitable Products
What Happens?
Step 1:
Filter removes products with negative profit.
Step 2:
TopCount selects the ten most profitable products.
This type of expression is commonly used in executive dashboards.
Prefer Dynamic subsets
Dynamic subsets reduce manual maintenance and improve flexibility.
Use Filter Carefully
Filtering very large dimensions can impact performance. Consider limiting the initial set whenever possible.
TopCount Is More Efficient Than Order + Head
Instead of:
Use:
TopCount is cleaner and generally performs better.
Test Expressions in the PAW Set Editor
The Set Editor provides a quick way to validate and troubleshoot MDX expressions before deploying them into books and dashboards.
MDX is one of the most valuable skills a TM1 developer can master. Functions such as TopCount(), Filter(), and Descendants() enable dynamic reporting and significantly reduce the effort required to maintain subsets and dashboards.
By incorporating these functions into your Planning Analytics applications, you can deliver smarter, more responsive solutions to your users.
In Part 2, we'll explore time intelligence functions, including:
ParallelPeriod()
PeriodsToDate()
YTD calculations
Rolling periods
Year-over-Year analysis
These functions are essential for financial reporting and variance analysis.
Are you using MDX extensively in your Planning Analytics applications? Which function do you find most useful? Share your thoughts in the comments.