Filtering by Measures in TM1

Overview
When building applications on Cognos TM1, there is often a need to filter something by data in another cube for a user. In regular database applications, this would some form of a JOIN clause (INNER, LEFT OUTER, RIGHT OUTER). TM1 does not have those constructs, but not to worry. This can be done just as easily. This could be used when building a subset, or when querying a cube or dimension. In this example, I will start with a basic MDX subset definition and add pieces to it until it will serve as the MDX for building a sorted subset, filtered by data in a separate cube.

For this example, let's assume the following setup
*  We have a cube called c.Account.Balance with four dimensions (d.Account, d.Measure, d.Time, d.Year). *  This cube holds financial accounting information for a small company
*  d.Account containts an income statement chart of accounts
*  d.Measure has only one element called Amount
*  d.Time has 12 elements. One for each month Jan...Dec.
*  d.Year has 3 elements. One for each fiscal year of interest, Y2013, Y2014, Y2015
* We want a list of all accounts with a January, Y2014 balance greater than $1000

Step 1 - Build a simple dimension subset of all members

{ TM1SUBSETALL( [d.Account] ) }

Step 2 - Restrict the subset to only leaf members. These are also known as N-level or bottom level members.

{ TM1FILTERBYLEVEL( { TM1SUBSETALL( [d.Account] ) } ,0) }

Step 3 - Use the MDX FILTER function to restrict...or join... the dataset. Note how we do NOT need to specify anything for the d.Account member. TM1 is smart enough to know we are building a set of accounts from a cube filter.

{ FILTER( 
  { TM1FILTERBYLEVEL( { TM1SUBSETALL( [d.Account] ) } ,0) }
 ,[c.Account.Balance].( [d.Measure].[Amount], [d.Time].[Jan], [d.Year].[Y2014]) > 1000)

Step 4 - Add sorting with the MDX ORDER function. 

{ORDER(
 { FILTER(
    { TM1FILTERBYLEVEL( { TM1SUBSETALL( [d.Account] ) } ,0) }
  ,[c.Account.Balance].( [d.Measure].[Amount], [d.Time].[Jan], [d.Year].[Y2014]) > 1000)

 , [d.Account] ASC ) }

Done!

Now we have build an MDX statement to give us a list of accounts with a January Y2014 balance greater than $1000, sorted by the Account name.