MDX statements

Introduction

MDX statements are what SQL queries are to relational databases, but instead acting on OLAP (multidimensional) databases. In the case of TM1, we will use them to query a dimension and its members. It is possible to make a set of dimension elements based on filters. Or just the lowest level elements. Well …, a lot is possible.

Sources

Writing MDX statements will not be that easy, certainly not in the beginning. Where can you find help ?

  1. This very page you are looking at ! In all honesty and humbleness, it contains very good material.
  2. This high-quality overview for more details
  3. Microsoft documentation on the topic. Also, see member properties and several related pages.
  4. TM1 has a 'Record expression' functionality:

  5. Record the relevant steps, much like the Macro recorder in MS Office applications - Visual Basic for Applications or VBA for short.

MDX use cases

As a TM1 developer, we mainly use MDX statements in the following cases:

  • to make dynamic subsets rather than static subsets. For example, a cubeview showing the sales of the customers who bought last month as well (and only those customers);
  • to create custom views in a TI process. We will often zero out (part of) a cube, this is clearing cells at the lowest level elements in certain dimensions. This list of elements can be generated with no need to loop through the dimension ourselves.
  • Active forms in Excel have dynamic row subsets, you can expand and contract consolidations. The list of elements in the rows can again be an MDX statement, even quite complex statements.

Mind the syntax, it is crucial to get that straight. If not, TM1 won‘t like it… Some of the statements can be generated in the Subset Editor: turn on the Properties Window (menu View), then choose Tools > Record Expression. Record the expression while you do the steps manually. Stop recording when you are finished.

Often used MDX statements

Below, I will present the MDX statements I need most of the time. This page will therefore serve as a kind of code library: we can copy/paste and adjust only dimension names and element names. You will notice [] around dimension names and element names. They could be left out, unless (for instance) the name contains a space. Then we must enclose the name with []. The expressions are case-insensitive. Except for function names, the expressions are also spase-insensitive.

  • 1 hard-coded element (dimension name and element name follow):
    {[Project_type].[All types]}
    or:
    {[FIN_Year].[2019]}

  • In a TI process, we often use a parameter for the year or scenario:
    SubsetCreatebyMDX('Subset_Years', '{[FIN_Year].[2019]}');
    becomes:
    SubsetCreatebyMDX('Subset_Years', '{[FIN_Year].['|pYear|']}');

    The function EXPAND in Turbo Integrator can be used to change variable names to their values: SubsetCreatebyMDX('Subset_Years', EXPAND('{[FIN_Year].[%pYear%]}'));

  • 2 hard-coded elements:
    {[FIN_Year].[2018],[FIN_Year].[2019]}

  • If you want to use MDX statements in Excel or TM1 Web, you can use cell references in the following way:
    ="{[FIN_Year].[" & E15 & "],[FIN_Year].[" & E16 & "]}"

    This allows you to customize your reports to a great extend. For example, the user can sort the rows ascending or descending, where these 2 options are a selection in a cell. The choice of the user is then picked up in the MDX expression at hand.

  • All lowest level elements in a dimension:
    {TM1FilterByLevel( TM1SubsetAll( [Project_type] ), 0)}

  • All elements at certain levels:
    {TM1FilterByLevel( TM1SubsetAll( [Project_type] ), 1,2,3)}

  • All consolidated elements in a dimension:
    { Except( TM1SubsetAll( [Project_type] ), { TM1FilterByLevel( TM1SubsetAll( [Project_type] ), 0) } )}

  • Instead of TM1SubsetAll, you can also use Members:
    {[Project_type].Members}

  • All lowest level elements in a dimension, sorted alphabetically:
    {TM1Sort( TM1FilterByLevel( TM1SubsetAll( [Project_type] ), 0), Asc)}

    Next to Sort, Order is also supported.

  • If you would like to Order elements based on an alias or other attribute:
    Order({[Month].Members}, [Month].[Short monthnr], Asc)

    Short monthnr is an alias in this case.

  • Order can be nested, so if you want to sort on 3 measures, in order (no pun intended), you can put Order inside a second Order inside a third Order:
    Order(Order(Order({[Employee].Members}, [Employee].[Department], Asc), [Employee].[Age], Asc), [Employee].[Salary], Asc)

  • All lowest level elements in a dimension, except one element:
    { Except( TM1FilterByLevel( TM1SubsetAll( [Work_Customers] ), 0), {[Work_Customers].[Office]} ) }

  • All lowest level elements in a dimension except those elements below a certain consolidation:
    { Except( TM1FilterByLevel( TM1SubsetAll( [Fct_Product] ), 0), Descendants( [Fct_Product].[My consolidated product] ) )}

  • An element and its Descendants all the way down the hierarchy:
    {Descendants([FIN_Account].[EBIT]) }

  • All Descendants of an element, without that element:
    {Except( Descendants([FIN_Account].[EBIT]), {[FIN_Account].[EBIT]} )}

  • Leaf-level Descendants of a given element:
    {TM1FilterByLevel( Descendants([FIN_Account].[EBIT]), 0)}
    or:
    {TM1FilterByLevel( TM1DrilldownMember( [FIN_Account].[EBIT], ALL, Recursive), 0)}

    You can easily sort the returned elements (here, ascending):
    {TM1Sort( TM1FilterByLevel( Descendants([FIN_Account].[EBIT]), 0), Asc)}

    Leaving out the filter by level will return the consolidated element(s) as well. So, Descendants and TM1DrilldownMember both include the parent element that was drilled upon.

  • A typical month selection where the user could choose a consolidated element - parameter pMonth:
    SubsetCreateByMDX( vSubset, '{TM1FilterByLevel( Descendants( [Month].[' | pMonth | ']), 0)}', 'Month' );
    
  • An element and its descendants all the way down the hierarchy, but omitting level 0:
    {Except( Descendants([Customer].[Total Customer]), {TM1FilterByLevel( Descendants([Customer].[Total Customer]), 0 )} )}

  • Leaf-level descendants of 2 consolidated elements:
    {TM1FilterByLevel( Union ( Descendants( [FIN_Account].[EBIT] ), Descendants( [FIN_Account].[Taxes] )), 0)}

  • Leaf-level descendants of a given consolidated element, that consolidation included:
    {Union( {FIN_Account.[EBIT]}, TM1FilterByLevel( Descendants(FIN_Account.[EBIT]), 0) )}
    or:
    {FIN_Account.[EBIT], TM1FilterByLevel( Descendants(FIN_Account.[EBIT]), 0) }
    Note: The Union function can be shortened to the + operator (an element is listed only once):
    {FIN_Account.[EBIT]} + {TM1FilterByLevel( Descendants(FIN_Account.[EBIT]), 0)}
    or, now EBIT is at the bottom of the list, the Order of the elements in the expression is retained left to right:
    {{TM1FilterByLevel( Descendants(FIN_Account.[EBIT]), 0)}, FIN_Account.[EBIT] }

    Next to Union, Intersect is also supported. Sidenote: if you want to avoid the removal of duplicate elements, use a comma instead of a plus sign. Example: [Fct_Product].[A], [Fct_Product].[B], [Fct_Product].[C], TM1FilterByLevel( Descendants([Fct_Product].[C]), 0)}

  • The parent elements of an element including the element itself, can be retrieved with Ascendants:
    {Ascendants([Fct_Customer].[111522])}

    Note that only the parents in the first dimension rollup are retrieved.

  • Much better would be to list all parent elements of an element in the entire dimension. The element drilled up upon is 'INPUT Sales third parties' in the dimension 'Rp_Cost_Element'.
    Filter( Except(TM1SubsetAll( [Rp_Cost_Element] ), TM1FilterByLevel( TM1SubsetAll( [Rp_Cost_Element] ), 0 )), TM1TupleSize( Intersect({[Rp_Cost_Element].[INPUT Sales third parties]}, Descendants([Rp_Cost_Element].CurrentMember)).Item(0)) > 0)

    Thank you Ty Cardin

  • The top parent of an element would then be:
    {Tail( {Ascendants([Fct_Customer].[111522])}, 1 )}

    In TM1 an element may have multiple parents within a hierarchy so if there are multiple parent branches you may need the intersect between the ancestors and all top node elements in the dimension:
    {Intersect( {Ascendants([Fct_Customer].[111522])}, {Filter( {TM1SubsetAll( [Fct_Customer] )}, [Fct_Customer].CurrentMember.Parent.Name = "")})}

  • Union can be interesting if you have a subset that should "expand above" but there are other elements that should be shown at the top:
    {Union( {[Account].[Earnings Before Taxes] }, {[Account].[Subset Exp Above] })}

    Here the subset 'Subset Exp Above' is a second (public) subset on the same dimension: { Except( {TM1DrillDownMember( {[Account].[Earnings Before Taxes] }, ALL, RECURSIVE )}, { [Account].[Earnings Before Taxes] })}
    Thanks to Paul Simon for posting.

  • Intersect is useful to evaluate two sets of elements:
    {Intersect( Filter( TM1SubsetAll( [Month] ), [Filter].([Filter].[FilterOrder]) <> 0), Descendants(Month.[Q1]) )}

  • Another example on Intersect:
    {Intersect( Tm1SubsetToSet( [Account], "Cost accounts" ), Tm1SubsetToSet( [Account], "Filter by level 0" ) )}

  • We can automate the previous subset in Turbo Integrator as follows:
    vDim = 'Account';
    
    vSubset1 = 'Cost accounts';
    vSubset2 = 'Filter by level 0';
    vSubset3 = 'Input cost accounts';
    
    # Create MDX
    vMdx = '{Intersect( Tm1SubsetToSet( [' | vDim | '], "' | vSubset1 | '" ), Tm1SubsetToSet( [' | vDim | '], "' | vSubset2 | '" ) )}';
    
    SubsetCreateByMDX( vSubset3, vMDX, vDim );
    
  • The immediate children (leaf level or consolidated) of a given element:
    {[Project_type].[All Types].Children}

  • An element together with its immediate children (leaf level or consolidated):
    {DrilldownLevel( {[Project_type].[All Types]} )}

  • Alternatively:
    {TM1DrillDownMember( {[Project_type].[All Types]}, ALL )}

  • Sometimes we require a consolidated element with all its Descendants, together with a different consolidation with only that one's immediate children:
    { Descendants([Fct_Assumption].[Total Assumption]) } + { DrilldownLevel( [Fct_Assumption].[Subtotals]) }



  • Similar to DrilldownLevel, but you limit the list to the first x elements:
    {DrilldownLevelTOP( [Project_type].[All Types], 3)}

    DrilldownLevelBOTTOM is similar to this function.

  • FIRSTCHILD gives us the first child of the element:
    {[Month].[Q2].FirstChild}

    … will return Apr. LASTCHILD gives us Jun.

  • FIRSTSIBLING gives us the first child of the shared parent:
    {[Month].[Q2].FirstSibling}

    … will return Q1. LASTSIBLING gives us Q4.

  • HEAD gives us the first element of a set:
    {HEAD( {[Month].[Q1].Children}

    … will return Jan. TAIL gives us Mar.

  • LAG returns the element that is a specified number of positions along the element’s level:
    {[Month].[Q3].Lag(2)}

    … will return Q1.

  • LEAD is the inverse of the function LAG:
    {[Month].[Q3].Lead(1)}

    … will return Q4.

    Lag(1) is equivalent to Lead(-1), since the functions are inverse.
  • PREVMEMBER is equal to LAG(1) and NEXTMEMBER is LEAD(1):
    {[Month].[Q3].PrevMember}

    … will return Q2.

  • We have also functions to roll up from elements to their parent(s), for example Parent, Ancestors or TM1Rollup:
    {TM1Rollup( {[Month].[Jan]}, {[Month].[Jan]} )}

    returns the parents of the month of January in the Month dimension. This expression mimicks the rollup button in the Subset Editor, by the way. It can be similar to a loop over Elpar from 1 to ElparN.

  • Ancestor can be useful to ask for the parents of an element, going from parent to grandparent to grand-grandparent, etc:
    {Ancestor([Rp_Account].[600000], 0)}, {Ancestor([Rp_Account].[600000], 1)}, {Ancestor([Rp_Account].[600000], 2)}, {Ancestor([Rp_Account].[600000], 3)}, …

    will return the element itself, then the parent, grandparent, parent of the grandparent.

  • LASTPERIODS returns the n elements preceding and including the selected element along the element’s level
    {LastPeriods( 3, [Month].[Q3])}

    … will return Q1, Q2, Q3.


    Please note that LastPeriods is not restricted to time dimensions. It can be utilized in a similar fashion on any dimension irrespective to what element names are used.

  • LEVEL returns the elements at the same level as the chosen element:
    {[Month].[Q3].Level.Members}

    … will return Q1, Q2, Q3, Q4.

  • MEMBERS allows us to select a range of contiguous elements (following dimension indexes) from the same level specifying the first and last element of the set with a colon between them:
    {[Month].[Jan]:[Month].[Oct]}

    … will return Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct.

      Please note the 2 following requirements:
    • Both elements should be at the same level in the dimension
    • Index-wise, no dimension elements at a different level, can be between the 2 listed elements
  • LOOKUPCUBE allows to filter on elements following information in a cube:
    {TM1FilterByPattern( TM1SubsetAll([Account_PL]), LookUpCube ( "Prm_Account", "([Prm_Account_dim1].[Pattern], [Prm_Account_Msr].[Text])" ) )}

    … will filter all elements in the Account_PL dimension following the pattern mentioned in the lookup cube Prm_Account.

  • A simpler syntax:
    {TM1FilterByPattern( TM1SubsetAll([Account_PL]), [Prm_Account].([Prm_Account_dim1].[Pattern], [Prm_Account_Msr].[Text]))}

  • A more useful example would be to return in a subset the month name that is entered in a lookup cube. For instance, which month and year are subject to being closed financially ?
    {TM1FilterByPattern( TM1SubsetAll([Month]), [Prm_Finance].([Prm_Fin_Param].[Month of close], [Prm_Finance_Msr].[Text]))}

  • But now it gets interesting ! To the best of my knowledge, there aren't many ways to have a dynamic subset on a Months dimension return for example both a month and its year to date (YTD) counterpart. Like, "Nov" and "YTD_Nov" where "Nov" is input in a central lookup cube.



    This can be done, however, with similar syntax:
    {Union( TM1FilterByPattern( TM1SubsetAll([Month]), [Prm_Finance].([Prm_Fin_Param].[Month of close], [Prm_Finance_Msr].[Text])), TM1FilterByPattern( TM1SubsetAll([Month]), "YTD_" + [Prm_Finance].([Prm_Fin_Param].[Month of close], [Prm_Finance_Msr].[Text])))}

    Alternatively you can use the LookUpCube function:
    {Union( TM1FilterByPattern( TM1SubsetAll([Month]), LookUpCube( "Prm_Finance", "([Prm_Fin_Param].[Month of close], [Prm_Finance_Msr].[Text])" )), TM1FilterByPattern( TM1SubsetAll([Month]), "YTD_" + LookUpCube( "Prm_Finance", "([Prm_Fin_Param].[Month of close], [Prm_Finance_Msr].[Text])" )))}

    Syntax 1 definitely has my vote, since it is somewhat shorter, but most of all because we avoid the double quote character ".

    You can of course use Turbo Integrator to create/maintain subsets but honestly, this MDX-based dynamic solution certainly has merits, and is something I would add to TM1 models to increase the usefulness to the TM1 user community.

    You could use a wildcard to cut down on the length of the expression, but beware that this could lead to additional elements in the subset which might not be wanted:
    {TM1FilterByPattern( TM1SubsetAll([Month]), "*" + [Prm_Finance].([Prm_Fin_Param].[Month of close], [Prm_Finance_Msr].[Text]))}

  • Or, yet another method, getting the reporting year for Actuals from a parameter cube:
    {StrToMember("[Rp_Year].["+[Rp_Irp_Parameter].([Rp_Scenario].[Actual],[Rp_Irp_Parameter].[Reporting Year],[Rp_Irp_Parameter_Msr].[Value])+"]") }

  • Going further, a way to filter elements in a dimension based on cube data, where the definition of the cube cells is stored in a parameter cube:
    {Filter( TM1FilterByLevel( TM1SubsetAll( [Period] ), 0), [Periods 2D].( StrToMember("[Year].[" + [System].([System_Prm].[Year], [System_Msr].[Text]) + "]")) > 0 )}

    Hereby, we filter elements in the dimension 'Period' based on data in the cube 'Periods 2D'. In that cube, we select cells based on a year that is stored in the cube 'System'.

  • A real-world example, taken from the TM1 forum. We want to dynamically filter projects for the logged on user, whereby a cube stores the access of users to sites. Site is an attribute of Project:
    {Filter( {Filter({TM1SubsetAll([Project])}, [Project].[Primary Site] <> "" )}, [z_Security_Site].( StrToMember( "[}Clients].["+USERNAME+ "]"), StrToMember("[Site].[" + [Project].[Primary Site] + "]"), [z_Security_Control_Measure].[Write_Flag]) > 0 )}

  • Filtering elements on a String attribute (alias or text attribute) could be achieved with a Property:
    {Filter( TM1SubsetAll( [FIN_Account] ), [FIN_Account].CurrentMember.Properties("Type") = "PL")}

  • The same result can be obtained as follows:
    {Filter( TM1SubsetAll( [FIN_Account] ), [FIN_Account].[Type] = "PL")}

    The drawback is that this syntax could be ambiguous, if 'Type' is an element or subset or attribute of the dimension. The previous syntax is better.

  • Another variation, here to retrieve versions that were created by a certain user:
    {Filter( {[version].members}, [version].[created by user] = StrToMember( "[}Clients].[" + UserName + "]").Properties('}TM1_DefaultDisplayValue'))}

  • The lookup value for the attribute can also be stored in a cube:
    {Filter( TM1SubsetAll( [FIN_Account] ), [FIN_Account].[Type] = [Prm_Account].([Prm_Account].[Parameter_Value],[Prm_Account_Measures].[Account type]))}

  • Filtering elements on a Numeric attribute:
    {Filter( TM1SubsetAll( [FIN_Account] ), [FIN_Account].[Conversion] = 1)}

  • Filtering those elements at any level that contain an alias:
    {Filter( TM1SubsetAll( [FIN_Account] ), [FIN_Account].[Description] <> "")}

  • Filtering those elements where an attribute contains a certain text:
    {Filter( TM1SubsetAll( [FIN_Account] ), Instr( UCase([FIN_Account].[Description]), "AMORTIZATION" ) > 0 )}

    Note that the MDX function Instr is case-sensitive. Hence the use of the function UCase. More information can be obtained here

  • Filtering those elements whose cells in a cube contain a certain text:
    Filter( TM1FilterByLevel( TM1SubsetAll( [CostCenter] ), 0), Instr([Prm_CostCenter].([CostType].[No type], [Prm_CostCenter_Msr].[Manager Name]), 'jones' ) > 0)


  • Filtering those elements whose values in a cube are lower than the average over all elements. For instance, the customers who bought more than the average of all customers:
    {Filter( TM1FilterByLevel( TM1SubsetAll( [Customer] ), 0), ([Sales].[Sales_Msr].[Amount] > Avg( TM1FilterByLevel( TM1SubsetAll( [Customer] ), 0), [Sales].([Sales_Msr].[Amount])) ))}

  • A very useful trick involving Instr is the following. You can apply it to views in the Cube viewer, Planning Analytics, Active forms, and so on. Well, you can filter (e.g.) accounts in the rows of a view in the cube viewer, when the account prefix equals the company name. Company is another dimension in the cube:
    {Filter( TM1SubsetAll( [St_Account] ), Instr( [St_Account].CurrentMember.Name, [Mp_Std_Company].([Mp_Std_Company_Msr].[Company]) ) = 1 )}
    • St_Account is the dimension of accounts in the rows
    • Mp_Std_Company is the cube where the companies are mapped (a 2D cube with companies and a measure called "Company"). Below, China Guangzhou is mapped in the lookup cube to "C010", which filters the prefix in the accounts)



    This is based on the information in this topic.

  • Filtering the currency dimension when the user has a currency attribute filled in:
    { Filter( {TM1SubsetAll([Dim_Currency])}, [}ClientProperties].( StrToMember("[}Clients].["+USERNAME+"]"), [}ClientProperties].[currency]) = [Dim_Currency].CurrentMember.Name )}
    • Dim_Currency is the dimension of currencies, it could be anywhere in the view, active form, ...
    • currency is an attribute added to the }ClientProperties cube, it should match the currencies in the dimension Dim_Currency

  • Filtering the level 0 descendants of elements marked with a certain attribute:
    {TM1FilterByLevel( Descendants( Filter( TM1SubsetAll( [CostCenter] ), [CostCenter].[Overhead CC] = "Y" ) ), 0)}


  • Filtering using wildcards:
    {TM1FilterByPattern( TM1FilterByLevel( TM1SubsetAll( [FIN_Account] ), 0), "613?2*")}

  • Filtering using AND and OR:
    { Filter( TM1SubsetAll( [FIN_Account] ), ( ([FIN_Account].[Type]="PL" AND [FIN_Account].[Conversion]=1) OR ([FIN_Account].[Description] <>"") ) ) }

  • Sorting the hierarchy following its consolidation structures:
    { Hierarchize( TM1SubsetAll( [CostCenter] ))}

  • Sorting the elements by index (all elements have an index):
    {TM1SortByIndex( TM1SubsetAll( [PL_Account] ), Asc)}

  • Generating a list of distinct element names:
    { DISTINCT( TM1SubsetAll( [PL_Account] ))}


    or based on an existing subset:
    { DISTINCT( TM1SUBSETTOSET ( [PL_Account], "YOUR_SUBSET_NAME" ))}
  • Months 'smaller' (earlier) than a threshold (variable) month (for example, copy Actuals to Budget scenario) (element names like 201809):
    {TM1Sort( Filter( TM1FilterByLevel( TM1SubsetAll( [Time] ), 0), [Time].CurrentMember.Name <= "' | pLastMonthOfActuals | '" ), Asc)}
    or (Code is an alias on the Time dimension):
    {TM1Sort( Filter( TM1FilterByLevel( TM1SubsetAll( [Time] ), 0), [Time].[Code] <= "' | pLastMonthOfActuals | '" ), Asc)}
    or (selecting a range of months, possibly spanning multiple years):
    {TM1Sort( TM1FilterByLevel( [Time].[201901]:[Time].[201909], 0), Asc)}

  • Selecting elements based on values in a cube (we do not have to specify every dimension in the cube):
    {Filter( TM1FilterByLevel( TM1SubsetAll( [PL_Account] ), 0), [BalanceSheet].([FIN_Year].[2019],[FIN_Period].[P10],[FIN_Scenario].[Actual],[BS_Measures].[Amount])<>0)}

    If we do not specify an element in a dimension of the cube, TM1 will use the element at index 1 in the dimension, unless we specify a DefaultMember in the }HierarchyProperties control cube. So watch out and make sure this is what you need.

    Topcount, bottomcount, topsum, bottomsum, toppercent and bottompercent could be done in a similar fashion:
    {TopCount( TM1FilterByLevel( TM1SubsetAll( [SalesPerson] ), 0), 5, [Sales].([Sales_Measures].[Amount]))}

    A practical use case for Order and topcount:
    {Order( TopCount( TM1FilterByLevel( TM1SubsetAll( [SalesPerson] ), 0), 10, [Sales].([Sales_Measures].[Amount])), [Sales].([Sales_Measures].[Amount]), BDesc)} Statements like TopCount or TopSum can be recorded with the interface, as well as sorting elements alphabetically:


    By the way, I discovered a nice trick to quickly see in which cubes a dimension is used: Go to the dimension's Subset Editor, then Tools > Filter... and the dropdown will show you the cubes containing the dimension.

    The HEAD function can be used to limit a subset to, say, its first 5 elements.

  • Selecting Products that have revenue but where an attribute is missing (entered by users or loaded from a source system):
    {TM1Sort( Filter( Filter( TM1FilterByLevel( TM1SubsetAll( [Product] ), 0), [Rpt_Sales].([Year_Month].[Total 2019], [Region].[Total Region], [Sales_Msr].[Revenue]) <> 0), [Product].[Sales_Rep] = "" ), Asc )}

    Note that this kind of dynamic subset is an ideal candidate to present in a view to users - one can see immediately for which products the attribute should be completed.
  • Selecting elements based on values in a cube and sort on the cube values:
    {[Customers].[All customers]} + {Order( Filter( TM1FilterByLevel( TM1SubsetAll( [Customers] ), 0), [Sales].([Sales_Measures].[Amount]) <> 0 ), [Sales].([Sales_Measures].[Amount],[Sales_Year].[2019]), Asc) }

  • Generate can be tricky but useful. It allows to apply an operation on every element in a first selection. Like in: select all level 1 customers (consolidated) and for each of them, find the customer with the highest revenue:
    {Generate( {TM1FilterByLevel( {TM1SubsetAll( [Customers] )}, 1)}, TopCount(Descendants([Customers].CurrentMember, 1), 1, Act_Sales.([Act_Sales_Msr].[Revenue])))}

  • A second example, list all managers of employees in a subset. Manager names are part of the same dimension and are entered against the employee name in a 3D-cube called "Employee Planning" (StrToMember will be discussed later on):
    {Generate( Filter( TM1SubsetAll( [Employee] ), [Employee Planning].([Scenario].[RP 2019],[Employee Planning Measures].[Manager ID]) <> ""), {StrToMember("[" + [Employee Planning].([Scenario].[RP 2019],[Employee].CurrentMember,[Employee Planning Measures].[Manager ID]) + "]")})}

  • Orphans in the dimension (definition: level 0 elements without parent consolidation), but ONLY n type elements:
    {Filter( TM1FilterByLevel( TM1SubsetAll([Accounts]), 0), [Accounts].CurrentMember.Parent.Name = "" )}

    Or:
    {Filter( TM1FilterByLevel( TM1SubsetAll([Accounts]), 0), Count([Accounts].CurrentMember.Ancestors) = 0)}

    Or:
    {Filter( Filter( TM1SubsetAll([Accounts]), [Accounts].CurrentMember.Parent.Name=""), ISLEAF( [Accounts].CurrentMember ))}

  • Orphans in the dimension (definition: level 0 elements without parent consolidation), both n elements and consolidations:
    {Filter( TM1SubsetAll([Accounts]), [Accounts].CurrentMember.Parent.Name = "" )}

  • Top level consolidations:
    {TM1SortByIndex( Except( Filter( TM1SubsetAll( [Accounts] ), [Accounts].CurrentMember.Parent.Name = "" ), {TM1FilterByLevel( TM1SubsetAll( [Accounts] ), 0 )} ), Asc)}

  • Top level consolidations containing a certain element (account 600000):
    Filter( Filter( TM1SubsetAll([Accounts]), [Accounts].CurrentMember.Parent.Name = ''), TM1TupleSize(Intersect({[Accounts].[600000]}, Descendants({[Accounts].CurrentMember})).Item(0)) > 0 )

    This expression features the function TM1TupleSize to return the number of members in a tuple.

  • Consolidations without children:
    {Filter( TM1FilterByLevel( TM1SubsetAll([Accounts]), 1), ISLEAF( [Accounts].CurrentMember ))}

    Alternatively:
    {Filter( TM1FilterByLevel( TM1SubsetAll([Accounts]), 1), [Accounts].CurrentMember.FirstChild.Name = "")}

    Alternatively:
    {Filter( TM1FilterByLevel( TM1SubsetAll([Accounts]), 1), Count([Accounts].CurrentMember.Children) = 0)}

    Alternatively:
    {Filter( TM1FilterByLevel( TM1SubsetAll([Accounts]), 1), TM1TupleSize( ([Accounts].CurrentMember.Children).Item(0)) = 0)}

    With the TM1 REST API:
    /api/v1/Dimensions('Accounts')/Hierarchies('Accounts')/Members?$select=Name&$expand=Element($select=Type),Children/$count&$filter=Element/Type eq 3 and Children/$count eq 0
    Source

  • The current TM1 username in the }Clients dimension (call this subset whoami):
    {StrToMember("[}Clients].[" + USERNAME + "]")}

    Alternatively:
    {TM1FilterByPattern( TM1SubsetAll( [}Clients] ), USERNAME )}

  • Using this notion of a username we can also use for the user (given a subset whoami as defined above):
    {[}Clients].[whoami].Item(0)}

    Or:
    TM1SubsetToSet([}Clients], "whoami")

    Or:
    {TM1Member([}Clients].[whoami].Item(0),[}Clients])}

  • Selecting customers that have an outstanding balance in the current month:
    Filter({TM1FilterByLevel( {TM1SubsetAll( [Customer] )}, 0)}, [Invoice].(TM1Member([Time].[Invoice Month].Item(0),0),[Invoice_measures].[Outstanding balance]) > 0)

  • The list of security groups to which a client (vUser) has been assigned:
    { Filter ( [}Groups].Members, [}ClientGroups].( [}Clients].[' | vUser | '] ) <> "" )}

    Note that the resulting subset could be empty.
  • All application cubes in the TM1 model:
    {TM1Sort( Except( TM1SubsetAll( [}Cubes] ), {TM1FilterByPattern( TM1SubsetAll( [}Cubes] ), "}*" )} ), Asc)}
    Likewise for processes, dimensions, … {TM1Sort( Except( TM1SubsetAll( [}Cubes] ), {TM1FilterByPattern( TM1SubsetAll( [}Dimensions] ), "}*" )} ), Asc)}

  • All control cubes in the TM1 model:
    {TM1Sort( {M1FilterByPattern( TM1SubsetAll( [}Cubes] ), "}*" ), Asc)}

    Likewise for processes, dimensions, …

When you look at the Microsoft links I mentioned above, you will find a lot more functions. Including functions for information on the dimension structure: FirstSibling, Parent, Children, FirstChild / LastChild, Ancestors, and so on. Many other options for creating dynamic subsets are explained on that webpage. It is a must see!

Temporary subsets

Needless to say, but these subset definitions using MDX syntax can be used in the function SubsetCreatebyMDX. That function can also create temporary subsets as of TM1 10.2.2 FP4 (see here).

Caveats

A few caveats apply. If you create a dynamic subset in TI (the function is called SubsetCreatebyMDX('NAME OF THE SUBSET', 'MDX EXPRESSION'), make sure you always have at least 1 element that matches. If not, the process errors out. Unless you use TM1 10.1 or later, where an undocumented 3rd parameter to the function equal to the dimension name, will avoid the error in case no elements satisfy the expression. As an alternative, in that case you should loop over the dimension elements and insert the matching elements in a static subset SubsetCreate('DIMENSION NAME', 'SUBSET NAME') and SubsetElementInsert('DIMENSION NAME', 'SUBSET NAME', 'ELEMENT NAME', POSITION). The latter technique will always work (unless you are stuck in an endless loop like me on some Monday mornings), whichever you will use depends on your liking and coding skills.

Please do NOT use the function TM1SubsetBasis() (unless you know what you are doing). Even though the MDX expression recorder generates such code, it is in fact not to be used. It refers to a relative selection of elements ('at the time of using the function') rather than an absolute selection of elements ('it will always be the same selection/result, all else being equal').

Excel

In Excel spreadsheets, we can work with MDX queries to return a subset: for example in active forms. There, you could store the MDX query in a (hidden) cell. The query expression could be chopped up to include cell references. For instance, the user can choose a Product family in cell H10, and the active form returns the data on the lowest-level children of that Product family. Mind that there could be a 256 characters limit for the MDX expression for older versions of TM1. You COULD leave out the dimension names BUT then the element names must be unique throughout the ENTIRE TM1 model (over all dimensions).




Homepage

Section contents

About Wim

Wim Gielis is a Business Intelligence consultant and Excel expert

Other links