Definition
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 )}Where:
- 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.
DESCENDANTS([Account].[Account].[TOTX])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.
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)
Altogether saved as Filter Expenses Set Expenses greater than 300k and clicking on Apply and close.
This then results in only those elements where expenses are greater than $300k.
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.
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.
