Did you know you can use MDX to create dynamic subsets based on cube values in IBM Planning Analytics? In our example we will only show companies with net sales greater than 30,000,000 in the selected year. 

An MDX statement that can provide this result may look something like this:

{FILTER({TM1SUBSETALL([bpmCompany])},[bpmFinance].([bpmScenario].CurrentMember, [bpmDepartment].CurrentMember,[bpmAccount].[Net Sales],[bpmCurrency].CurrentMember,[bpmPeriod].CurrentMember,[bpmFinance_Msr].[Balance Post-Alloc]) > 30000000 )}

The components of this statement are:

• FILTER({abc}),[Cube].([Dim1].[Value],[Dim2].[Value]…) > 30000000 )
  • Filter set ABC to only show members with a value greater than 30,000,000 for the intersection defined in the section after the comma
• TM1SUBSETALL([bpmCompany])
  • Grab all members within the bpmCompany dimension
• [bpmFinance]
  • The cube being used for the data
• ([bpmScenario].CurrentMember, [bpmDepartment].CurrentMember,[bpmAccount].[Net Sales],[bpmCurrency].CurrentMember,[bpmPeriod].CurrentMember,[bpmFinance_Msr].[Balance Post-Alloc])
  • The intersection used to get the cube value
  • This is like a DBRW formula in the sense that we are defining a view by listing every dimension and providing a member within that dimension
    • CurrentMember will grab whatever element is selected in the cube view for that dimension
    • The other dimensions are using a specific member that we always want to filter by

Below you will see only companies with net sales greater than 30,000,000 for the “Balance Post-Alloc” measure remain in the bpmCompany subset. This will dynamically update as selections for other dimensions are changed.

Revelwood has worked with IBM Planning Analytics / TM1 for more than 27 years. We’ve partnered with hundreds of companies on the design, development, maintenance and updates of IBM Planning Analytics applications, across every industry. Have a challenge with Planning Analytics / TM1? We can help you!

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: NumberToStringEx

IBM Planning Analytics Tips & Tricks: WildcardFileSearch

IBM Planning Analytics Tips & Tricks: Planning Analytics Workspace Visualization Axis