Exploring TM1 - a Chartertech Company
Close this search box.

Filter MDX Function in Planning Analytics


The TM1 MDX function FILTER in IBM Planning Analytics is utilised to selectively retrieve data from multidimensional cubes based on specified criteria. In conjunction with other MDX functions like TM1FILTERBYLEVEL or DESCENDANTS, users can dynamically filter data based on levels, hierarchies, or specific dimensions.

Syntax of Filter MDX

The basic syntax for Filter function is as follows:

{Filter( Set, Criteria )}


  • Set: is the set of elements we want to filter,
  • Criteria: is the filter criteria we will apply to the Set

Example of Filter MDX

In the example below we will set up the Set as the N level (or Input level, or level 0) elements of the Account dimension. Then we will filter those elements from the General Ledger cube using the Value measure, where it is greater than 100.

{FILTER({TM1FILTERBYLEVEL( {DESCENDANTS( [Account] )}, 0)},[General Ledger].([Measures].[Value]) > 100 )}

Complex Example of Filter MDX in TM1

Total Expenses Accounts

In this more complex example, we want to get the input level elements that are descendants of TOTX where Actuals are greater than 300,000.

FILTER(TM1FILTERBYLEVEL(DESCENDANTS([Account].[Account].[TOTX]) , 0) , [General Ledger].([Version].[Version].[Actual]) > 300000)

This code will pass the value “TOTX” (Total Expenses) into the descendants MDX function in conjunction with the MDX function TM1FILTERBYLEVEL on the lowest level of elements which in this case is 0 and then, a FILTER condition for the “Actual” element of our Version hierarchy.

In this dynamic set we are going to create, I am only interested in Actuals values for Expenses elements where the values is greater than 300,000.

There are three steps to achieve this.

Step 1 – Descendants

First, create a simple set using the Descendants function of TOTX. To understand more about Descendants, please see this post.


Using the Account Dimension to find the member TOTX, and as follow action, right click and Select Insert Member with Descendants.

You can see the TOTX members added to the right panel of Current Set .

Clicking on the MDX button, we can see the mdx expression written by Planning Analytics.

After saving the Set and clicking on Apply and close, we can verify the results on screen.

Planning Analytics for Excel Set Editor

Step 2 – Edit MDX to Create Additional Set applying a Filter condition

We’ll now use the set of elements for Total Expenses already created. First, we will apply the following MDX expression to return Actual Values where Expenses is greater than 300k.

The MDX expression for it would be as follows.

FILTER(TM1FILTERBYLEVEL(DESCENDANTS([Account].[Account].[TOTX]) , 0) , [General Ledger].([Version].[Version].[Actual]) > 300000)
Planning Analytics for Excel Set Editor

Altogether saved as Filter Expenses Set Expenses greater than 300k and clicking on Apply and close.

Planning Analytics for Excel Set Editor

This then results in only those elements where expenses are greater than $300k.

Planning Analytics for Excel Set Editor

Creating a Report

At the end, after producing the set we were looking for, we can have a report based only on Expenses Greater than 300k as shown.

Planning Analytics for Excel Set Editor

Video Guide to Filter MDX in TM1

In the video below, we will illustrate the simple methods shown above.

The video shows how to use the automated features of the Planning Analytics set editor to create a Total Expenses elements set with a Filter conditional.

Do you need Help with MDX?

If you’d like some help with MDX in TM1/Planning Analytics – be it a simple tip to correct some code, or some assistance building new reports or PAW dashboards, please get in touch. We’d be delighted to help.

  • This field is for validation purposes and should be left unchanged.

Post Sections

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *

Log In