ARITHMETIC OPERATORS 
Description/Notes 
Syntax 
Example 
+ Add, Positive 


/ Divide 


* Multiply 


 Subtract, Negative 


^ (Power) 





BITWISE OPERATORS 
AND 
Performs a logical conjunction on two numeric expressions. 
<Expression 1> AND <Expression 2> 
{ FILTER( {TM1SUBSETALL( [Cost Centre] )}, [Cost Centre].CurrentMember.Properties("Primary Cost Centre") <> "" AND [Cost Centre].CurrentMember.Properties("Division") <> "" ) }
Should give you Cost Centres where there is both a value in the Primary Cost Centre and Division attributes. 
NOT 
Performs a logical negation on a numeric expression. 
NOT <Expression 1> 
{ FILTER( {TM1SUBSETALL( [Cost Centre] )}, NOT [Cost Centre].CurrentMember.Properties("Primary Cost Centre") <> "" AND [Cost Centre].CurrentMember.Properties("Division") <> "" ) }
Should give you Cost Centres where the Primary Cost Centre attribute is blank and Division attributes is not blank. 
OR 
Performs a logical disjunction on two numeric expressions. 
<Expression 1> OR <Expression 2> 
{ FILTER( {TM1SUBSETALL( [Cost Centre] )}, [Cost Centre].CurrentMember.Properties("Primary Cost Centre") = "" OR [Cost Centre].CurrentMember.Properties("Division") = "" ) }
Should give you Cost Centres where Primary Cost Centre, Division attributes or both are blank. 
XOR 
Performs a logical exclusion on two numeric expressions. Returns True when only one expression evaluates to True. Either Expression 1 or Expression 2 is True, not both. 
<Expression 1> XOR <Expression 2> 
{ FILTER( {TM1SUBSETALL( [Cost Centre] )}, [Cost Centre].CurrentMember.Properties("Primary Cost Centre") = "" XOR [Cost Centre].CurrentMember.Properties("Division") = "" ) }
Should give you Cost Centres where either the Primary Cost Centre or the Division attributes are blank, but not both. 
IS 
Performs a logical comparison on two object expressions. Use square brackets if your level contains a space e.g. [Fiscal Year] Best to fully qualify using dimension and value e.g. [Period].[Quarters] where the name may appear in other dimensions causing ambiguity. 
<Expression 1> IS (<Expression 2>  NULL ) 
FILTER({TM1SUBSETALL( [Period] )}, [Period].CurrentMember.Level IS [Period].[Quarters])
Gives you Periods linked to the same level as which Quarters was defined. 



COMPARISON OPERATORS 
= 
Is Equal To  Applies to numeric and string comparisons 
<Expression 1> = <Expression 2> 
{ FILTER( {TM1SUBSETALL( [Cost Centre] )}, [Cost Centre].CurrentMember.Properties("Primary Cost Centre") = "" ) }
Returns a set of Cost Centres where the Primary Cost Centre attributes is blank. 
<> 
Is Not Equal to  Applies to numeric and string comparisons 
<Expression 1> <> <Expression 2> 
{ FILTER( {TM1SUBSETALL( [Cost Centre] )}, [Cost Centre].CurrentMember.Properties("Primary Cost Centre") <> "" ) }
Returns a set of Cost Centres where the Primary Cost Centre attributes is not blank. 
> 
Is Greater Than 
<Expression 1> > <Expression 2> 
{ FILTER( {TM1SUBSETALL( [Cost Centre] )}, [Expense].(...,[Amount]) > 50000 ) }
Returns a set of Cost Centres where specified value in the Expense cube is greater than 50,000 
>= 
Is Greater Than or Equal To 
<Expression 1> >= <Expression 2> 
{ FILTER( {TM1SUBSETALL( [Cost Centre] )}, [Expense].(...,[Amount]) >= 50000 ) }
Returns a set of Cost Centres where specified value in the Expense cube is greater than or equal to 50,000 
< 
Is Less Than 
<Expression 1> < <Expression 2> 
{ FILTER( {TM1SUBSETALL( [Cost Centre] )}, [Expense].(...,[Amount]) < 0 ) }
Returns a set of Cost Centres where specified value in the Expense cube is negative. 
<= 
Is Less Than or Equal To 
<Expression 1> <= <Expression 2> 
{ FILTER( {TM1SUBSETALL( [Cost Centre] )}, [Expense].(...,[Amount]) <= 0 ) }
Returns a set of Cost Centres where specified value in the Expense cube is zero or negative. 
TRUE 
Not an operator but a literal used in comparisons/filters 


FALSE 
Not an operator but a literal used in comparisons/filters 





CONCATENATION OPERATORS 
+ 
Combine or join multiple strings 
<String 1> + <String 2> 
{StrToMember("[Period].[Period].[" + [Client Assumption].( StrToMember("[}Clients].[" + UserName + "]"), [Client Assumption Measures].[Current Period]) + "]")}
Concatenates the value from an assumptions cube with the hierarchy to create a valid member. 



SET OPERATORS 
 (Except) 
Returns the difference between two sets, removing duplicate members. This operator is functionally equivalent to the Except function. 
<Set 1>  <Set 2> 
Did not work in a Dimension set but may work with a SELECT statement 
* (Crossjoin) 
Returns the cross product of two sets. This operator is functionally equivalent to the Crossjoin function. Used in an MDX view where you have dimensions stacked e.g. Product within Customer. 
<Set 1> * <Set 2> 
SELECT {TM1SubsetToSet([Expense Measures].[Expense Measures],"Default","public")} ON 0, {TM1SubsetToSet([Cost Centre].[Cost Centre],"Default","public")} * {TM1SubsetToSet([Reporting Currency].[Reporting Currency],"Default","public")} ON 1 FROM [Expense] WHERE ... 
: (Range) 
Returns a naturally ordered set, with the two specified members as endpoints and all members between the two specified members included as members of the set. 
<Member 1> : <Member 2> 
[Period].[Period].[2021JAN] : [Period].[Period].[2021DEC]
Should return periods from Jan to Dec 
+ (Union) 
Returns a union of two sets, excluding duplicate members. 
<Set 1> + <Set 2> 
{[Period].[Period].[2020JAN] : [Period].[Period].[2020DEC]} + {[Period].[Period].[2022JAN] : [Period].[Period].[2022DEC]}
Will return a set with the range of members from the first expression and second expression. Any overlaps do not result in duplicates. 



STRING FUNCTIONS 
Name 
Returns a string containing the member name 
<Member expression>.Name 
FILTER({TM1SUBSETALL( [Period] )}, RIGHT([Period].CurrentMember.Name,1)="V")
Returns elements where the Name of the member ends in a V e.g. 2021NOV 
Uniquename 
Returns a string containing the member unique name. Values are enclosed in square brackets. 
<Member expression>.UniqueName 
FILTER({TM1SUBSETALL( [Period] )}, INSTR(1, [Period].CurrentMember.UniqueName, "^", 0) = 0)
Returns members that are unique in the hierarchy i.e. do not form part of any other rollups and thus are not further qualified with a caret (^) 
Properties 
Returns a string, or a stronglytyped value, that contains a member property value i.e. Attribute value. 
Member_Expression.Properties(Property_Name [, TYPED]) 
{ORDER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Period] )}, 0)}, [Period].CurrentMember.Properties("Month"), BASC)}
Returns a set of leaf level months orders by Month which is numeric. 2021JAN, 2021FEB etc. do not sort chronologically naturally. 
Generate 
Executes a string function on each member of the set, almost like a For Each Member statement. 
Generate(<Set 1>, <Set 2> [, ALL]) 
{GENERATE( {[Period].[2023MAR]}, {[Period].CurrentMember, StrToMember("[Period].[" + [Period].CurrentMember.Name + " YTD]"), [Period].CurrentMember.Parent, [Period].CurrentMember.LAG(12)} )}
Should return 2023MAR itself, being the CurrentMember then 2023.09 YTD, 2023.FY and 2022APR 
UserName 
Returns the domain name and user name of the current connection. 
UserName 
{StrToMember("[Period].[Period].[" + [Client Assumption].( StrToMember("[}Clients].[" + UserName + "]"), [Client Assumption Measures].[Current Period]) + "]")}
UserName is the string value the active client and used to lookup the current period in the assumption cube. In this case, my value in the cube is 2021JAN and this is returned as a member. 
Instr 
Returns the position of the first occurrence of one string within another. NB: 4th parameter, compare in TM1 is used as 0 for casesensitive, 1 for ignore case. 
InStr([start, ]searched_string, search_string[, compare]) 
{FILTER( {[Period].[Period].Members}, InStr(1, [Period].[Period].CurrentMember.Name, "ju",1)>0 )}
Returns periods containing JU, Ju, jU or ju  remember parameter 4 to manage case. 
UCase 
Convert a string to upper case 
UCase(<string>) 
{FILTER( {[Period].[Period].Members}, InStr(1, UCase([Period].[Period].CurrentMember.Name), "JU",0)>0 )}
Returns periods containing JU e.g. 2021JUL 
LCase 
Convert a string to lower case 
LCase(<string>) 
{FILTER( {[Period].[Period].Members}, InStr(1, LCase([Period].[Period].CurrentMember.Name), "ju",0)>0 )}
Returns periods containing JU e.g. 2021JUL 
Left 
Return n number of characters from the start of a string 
Left(<string>,<count>) 
{FILTER( {[Period].[Period].Members}, LEFT([Period].[Period].CurrentMember.Name, 4)="2022" )}
Returns periods where the first 4 characters are 2022  could be N or C or S elements. 
Right 
Return n number of characters from the end of a string 
Right(<string>,<count>) 
{FILTER( {[Period].[Period].Members}, RIGHT([Period].[Period].CurrentMember.Name, 3)="JUL" )}
Returns periods where the last 3 characters are JUL  could be N or C or S elements. Does not appear case sensitive as it would include as a match 2021Jul, 2021jul too. 



CASTING FUNCTIONS 
MemberToStr 
Return a string containing the uniquename of the member 
MemberToStr(<Member>) 
{FILTER( {[Period].[Period].Members}, MemberToStr([Period].[Period].CurrentMember)="[Period].[2022.FY^2022JAN]" )} 
StrToMember 
Returns the member as specified in the string expression 
StrToMember(<string expression> [, CONSTRAINED]) 
STRTOMEMBER("[Period].[Period].[" + SETTOSTR( TM1SubsetToSet([Period].[Period] , "_SCurrent Year" , "public")) + "]" ) 
StrToValue 
Return the value of a specified string. NB: Will fail on Nulls and my require the use of IIF() to trap 
StrToValue(<string expression>) 
{FILTER( {[Period].[Period].Members}, StrToValue([Period].[Period].CurrentMember.Properties("LEVEL_NUMBER"))=0 )} 
SetToStr 
Returns a string based on the set expression. Will bring back values with braces and a comma e.g. {2022JAN}, you may need to trim these. 
SetToStr(<Set> ) 
STRTOMEMBER("[Period].[Period].[" + SETTOSTR( TM1SubsetToSet([Period].[Period] , "_SCurrent Fiscal Year" , "public")) + "]" ).NEXTMEMBER 
StrToSet 
Return a set as specified by the string in the set specification. MS MDX has CONSTRAINED as an optional 2nd parameter which is not supported in TM1 
StrToSet(<set specification> [, CONSTRAINED]) 
StrToSet( "[Period].[Period].Members" ) 
StrToTuple 
Returns a tuple as specified in the string expression 
StrToTuple(<string expression> [, CONSTRAINED]) 
Need an example using SELECT 
TupleToStr 
Returns a string containing tuples. Could be used to parse in other functions 
StrToTuple(<tuple expression>) 
Need an example using SELECT 
NameToSet 
Returns a set based on a member name 
NameToSet(<member name>) 
{NameToSet( "[Period].[Period].[2022.FY^2022JAN]")} 



LOGICAL FUNCTIONS 
IS 
Performs a logical comparison on two object expressions to see if they are exactly the same. 
<Expression1> IS <Expression2> 
No useful example of how to use: FILTER( {TM1SUBSETALL( [Period] )}, [Period].CurrentMember IS [Period].[2020JUL])) 
IIF 
Returns one of two values determined by a logical test. 
IIf(Logical_Expression, Expression1, Expression2) 
FILTER( {TM1SUBSETALL( [Period] )}, StrToValue( IIF([Period].CurrentMember.Properties("Period Index")="", "0", [Period].CurrentMember.Properties("Period Index")))>0) 
Case, When, Else, End 
Lets you conditionally return specific values from multiple comparisons. 
CASE <expression> WHEN <case 1> then <expression 1> WHEN.. ELSE <expression> END 
{ STRTOMEMBER("[Period].[" + CASE LEFT( RIGHT( SetToStr( {[Period].[Period].[_SCurrent Period].Item(0)} ), 10), 8) WHEN "2022JAN" then "2021.Q1" WHEN "2021APR" then "2021.Q2" ELSE SetToStr( {[Period].[Period].[_SCurrent Period].Item(0)} ) END +"]") } 
IsLeaf 
Test is a member is a Leaf element 
IsLeaf(<Member>) 
FILTER( {TM1SUBSETALL( [Period] )}, IsLeaf([Period].CurrentMember)) 
IsAncestor 
Test if a specified member is an Ancestor of another specified member. 
IsAncestor(<Member1>,<Member2>) 
FILTER( {TM1SUBSETALL( [Period] )}, IsAncestor([Period].CurrentMember,[Period].[2022.Q3])) Returns all Ancestors of 2022.Q3 
IsEmpty 
Test if a cell, property or value is empty 
IsEmpty(<Expression>) 
FILTER( {TM1SUBSETALL( [Period] )}, IsEmpty([Sales].([...])) 
IsSibling 
Test if a member is a sibling of another specified member. 
IsSibling(<Member>,<Sibling Member>) 
FILTER( {TM1SUBSETALL( [Period] )}, IsSibling([Period].CurrentMember,[Period].[2022.Q3])) 
IsGeneration 
Test if the specified Member is in the specified Generation (Level) 
IsGeneration(<Member>,<Generation>) 
FILTER( {TM1SUBSETALL( [Period] )}, IsGeneration([Period].CurrentMember,2)) 
IsNull 
Not supported yet 
ISNULL(MDX_expression,value_if_null) 
Would be useful to set blanks to "0" etc. avoids IIF() 
*Boolean values returned 






MEMBER FUNCTIONS AND EXPRESSIONS

AllMembers 
Returns a set that contains all members of the specified hierarchy or level. Will include all calculated members in that hierarchy or level. 
<hierarchy expression>.AllMembers <level expression>.AllMembers 
[Period].AllMembers or [Period].[level000].AllMembers or [Period].[Halves].AllMembers 
Members 
Returns the set of members in a dimension, level, or hierarchy. Returns Members, not Elements i.e. will repeat where element is in multiple rollups 
<dimension expression>.Members <hierarchy expression>.Members <level expression>.Members 
[Period].Members 
DefaultMember 
Return the default member for the dimension/hierarchy as specified in the }HierarchyProperties cube. 
<dimension expression>.DefaultMember 
{[Period].DefaultMember} 
Current 
Returns the current tuple from a set during iteration. 
<Set>.Current 
Need a useful example 
CurrentMember 
Returns the current member along a specified hierarchy during iteration. 
Hierarchy_Expression.CurrentMember 
FILTER( {TM1SUBSETALL( [Period] )}, IsLeaf([Period].CurrentMember)) 
NextMember 
Returns the next member in the level that contains a specified member. 
Member_Expression.NextMember 
{[Period].[2020JUN].NextMember} gives you 2020JUL 
PrevMember 
Returns the previous member in the level that contains a specified member. 
Member_Expression.PrevMember 
{[Period].[2020JUN].PrevMember} gives you 2020MAY 
Parent 
Returns the parent of a member. 
Member_Expression.Parent 
Returns the first parent of the member {TM1Member([Period].[Current Month].Item(0),0).Parent} 
Root 


Level 
Returns the Level of a member 
Member_Expression.Level 
[Period].[2021Q3].Level 
Levels 
Returns members from a specified Level using the Level Name or number 
Hierarchy_Expression.Levels( Level_Number ) or Hierarchy_Expression.Levels( Level_Name ) 
[Period].[Period].LEVELS("Quarters").MEMBERS or [Period].[Period].LEVELS(1).MEMBERS 
This 


Ancestor 
A function that returns the ancestor of a specified member at a specified level or at a specified distance from the member. 
Ancestor(Member_Expression, Level_Expression) or Ancestor(Member_Expression, Distance) 
Returns an Ancestor from n levels higher. This returns Total Years which is the parent of 2021 {Ancestor(TM1Member([Period].[Current Month].Item(0),0),2)} or Returns the Half that the period is in: {Ancestor([Period].[2021Q3^2021/07],[Period].[Halves])} 
Ancestors 
Returns a set of ALL ancestors 
Ancestors(Member_Expression, Level_Expression) Ancestors(Member_Expression, Distance) 
Returns Ascendants without the current member {TM1Member([Period].[Current Month].Item(0),0).Ancestors} 
Ascendants 
Returns all of the ancestors of a member from the member itself up to the top of the member’s hierarchy 
Ascendants(Member_Expression) 
Returns the Current Month and parent then its parent {Ascendants(TM1Member([Period].[Current Month].Item(0),0))} 
FirstSibling 
Returns the first child of the parent of a member. 
Member_Expression.FirstSibling 
Returns the first child in the rollup that the member is in i.e. 2021/01 in this case {TM1Member([Period].[Current Month].Item(0),0).FirstSibling} 
LastSibling 
Returns the last child of the parent of a specified member. 
Member_Expression.LastSibling 
Returns the first child in the rollup that the member is in i.e. 2021/12 in this case {TM1Member([Period].[Current Month].Item(0),0).LastSibling} 
FirstChild 
Returns the first child of a specified member. 
Member_Expression.FirstChild 
Returns 2021/01 given 2021 {[Period].[2021].FirstChild} 
LastChild 
Returns the last child of a specified member. 
Member_Expression.LastChild 
Returns 2021/12 given 2021 {[Period].[2021].FirstChild} 
Cousin 
Returns the child member with the same relative position under a parent member as the specified child member. 
Cousin( Member_Expression , Ancestor_Member_Expression ) 
Returns the same month in the specified year i.e. 2023/06: {Cousin([Period].[Period].[2021/06], [Period].[Period].[2023])} 
OpeningPeriod 
Returns the first sibling among the descendants of a specified level, optionally at a specified member. 
OpeningPeriod( [ Level_Expression [ , Member_Expression ] ] ) 
{OpeningPeriod([Period].[Period].[Quarters],[Period].[Period].[2022H2])} Returns 2022Q3 as this is the first Quarter in 2022H2 
ClosingPeriod 
Returns the member that is the last sibling among the descendants of a specified member at a specified level. 
ClosingPeriod( [ Level_Expression [ ,Member_Expression ] ] ) 
{ClosingPeriod([Period].[Period].[Quarters],[Period].[Period].[2022H2])} Returns 2022Q4 being the last Quarter in 2022H2. 
ParallelPeriod 
Returns a member from a prior period in the same relative position as a specified member. 
ParallelPeriod( [ Level_Expression [ ,Index [ , Member_Expression ] ] ] ) 
{ParallelPeriod([Period].[Period].[Quarters],4,[Period].[Period].[2022Q4])} Returns 2021Q4 which is 4 quarters prior 
Head 
Returns the first specified number of elements in a set, while retaining duplicates. 
Head(<Set> [ ,Count ] ) 
{HEAD([Period].[2020.FY].Children,6)} 
Tail 
Returns a subset from the end of a set. 
Tail(<Set> [ ,Count ] ) 
{TAIL([Period].[2020.FY].Children,6)} 
Lead 
Returns the member that is a specified number of positions following a specified member along the member's level. 
Member_Expression.Lead( Index ) 
{[Period].[2020JUL].LEAD(6)} Gives you 2021JAN 
Lag 
Returns the member that is a specified number of positions before a specified member at the member's level. 
Member_Expression.Lag(Index) 
{[Period].[2020JUL].LAG(6)} Gives you 2020JAN 
Value 
Default property of a member 





TUPLE FUNCTIONS 
Current 
Returns the current tuple from a set during iteration. 


Item 
The Item function returns a member from the specified tuple. 
TM1Member([Period].[Current Year].ITEM(1),0 ) 



SET FUNCTIONS AND EXPRESSIONS

AddCalculatedMembers 
Returns a set generated by adding calculated members to that specified set. 

Example needed 
Children 
Returns the set of children of a specified member. 
Member_Expression.Children 
Returns the months in the quarter in this case: [Period].[2021Q3].Children 
Siblings 
Returns the siblings of a specified member, including the member itself. 
Member_Expression.Siblings 
Return all periods in the year based on current month. {TM1Member([Period].[Current Month].Item(0),0).Siblings} 
Descendants 
Review parameters e.g. SELF, ABOVE, LEAVES etc. 
Descendants(Member_Expression [ , Level_Expression [ ,Desc_Flag ] ] ) 
Descendants(TM1SubsetToSet([Period].[Period], "FiscalPeriods" , "public" ),4,Leaves) 
Members 
Retrieves a set containing all of the members from a dimension, hierarchy, or level. 
Dimension_Expression.Members Hierarchy_Expression.Members Level_Expression.Members 
{[Period].Members} {[Period].[Halves].Members} In the first MDX, all members in the Period dimension are returned. In the second, members in the Halves level are returned. 
Distinct 
Evaluates a specified set, removes duplicate tuples from the set, and returns the resulting set. 
Distinct(<Set>) 
DISTINCT({[Period].[2020.FY].Children,[Period].[2020.Q3].Children}) The set would have repeated 2020JAN, 2020FEB and 2020MAR but removed using Distinct 
Hierarchize 
Organize members into the Hierarchical order. 
Hierarchize(<Set> [ , POST ] ) 
{Hierarchize([Period].[Halves].Members + [Period].[Fiscal Years].Members,POST)} 
Union 
Returns a set that is generated by the union of two sets, optionally retaining duplicate members. 
Union(<Set 1>, <Set 2>[, ALL]) 
{UNION( [Period].[2020.FY].Children,[Period].[2021.FY].Children )} Combines set 1 and set 2 {UNION( [Period].[2020.FY].Children, {[Period].[2020JAN]}, ALL )} Will combine the sets and in this case have a duplicate entry for 2020JAN 
Intersect 
Returns members appearing in both sets. Duplicates can optionally be retained using the ALL parameter 
Intersect(<Set>1 , Set_Expression2 [ , ALL ] ) 
INTERSECT( TM1SUBSETALL( [Period] ), TM1SubsetToSet( [Period], "_SQuarter", "Public" ), ALL ) 
Except 
Evaluates two sets and removes those tuples in the first set that also exist in the second set, optionally retaining duplicates. 

{Except( {[Period].[2020.FY].Children}, {[Period].[2019NOV],[Period].[2019DEC]} )} Will return all months then exclude Nov and Dec 
Extract 
This is the opposite of the CrossJoin function and returns a set of tuples from the extracted hierarchy elements. 
Extract(<Set>, Hierarchy_Expression1 [,Hierarchy_Expression2, ...n] ) 
Need an example using a Select statement 
Filter 
Exclude members based on criteria 

FILTER( {TM1SUBSETALL( [Scenario] )}, VAL([Scenario].CurrentMember.Properties("Flag"))=1) 
Order 
Order contents of a set ASC, DESC, BASC, BDESC Note that ASC and DESC sorts within the member's parent. BASC and BDESC breaks the hierarchies and sort the elements like a flat list. 

{ORDER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Period] )}, 0)}, [Period].CurrentMember.Properties("Month"), BASC)}
Returns a set of leaf level months orders by Month which is numeric. 2021JAN, 2021FEB etc. do not sort chronologically naturally. 
Subset 
Skip N number of members and returns the remainder like the MID function for characters. Position is zerobased. 
Subset(<Set>, Start [ ,Count ] ) 
SUBSET( {TM1SubsetToSet([Period],"_SAll N","public")},5,4) In this example element 5 is 2019May, so 2019May and the next 3 elements are returned. 
LastPeriods 
Returns a set of members up to and including a specified member. 
LastPeriods(Index [ ,Member_Expression ] ) 
LastPeriods(4,[Period].[Period].[2022/08]) Returns 2022/08 and the prior 3 periods  4 members in all 
PeriodsToDate 
Returns a set of sibling members from the same level as a given member, starting with the first sibling and ending with the given member, as constrained by a specified level in the Time dimension. 
PeriodsToDate( [ Level_Expression [ ,Member_Expression ] ] ) 
PeriodsToDate([Period].[Period].[Years],[Period].[Period].[2022Q3^2022/08]) Returns 2022/01 and all periods to 2022/08 
BottomCount 
Sorts a set in ascending order, and returns the specified number of tuples in the specified set with the lowest values. 
BottomCount(<Set>, Count [,Numeric_Expression]) 
BOTTOMCOUNT( TM1SubsetToSet([model].[model] , "_SAll N" , "public") , 10.0 , [SalesCube].([actvsbud].[Actual], [region].[Argentina], [account1].[Units], [Month].[1 Quarter])) Sorts and returns bottom 10 models based on units sold  lowest sales (SData) 
BottomPercent 
Sorts a set in ascending order, and returns a set of tuples with the lowest values whose cumulative total is equal to or greater than a specified percentage. 
BottomPercent(<Set>, Percentage, Numeric_Expression) 
BOTTOMPERCENT( TM1SubsetToSet([model].[model] , "_SAll N" , "public") , 10 , [SalesCube].([actvsbud].[Actual], [region].[Argentina], [account1].[Units], [Month].[1 Quarter])) Returns models where the aggregate value is within 10% of the total for all models 
BottomSum 
Sorts a set and returns the bottom most elements whose cumulative total is at most a specified value. 
BottomSum(<Set>, Value, Numeric_Expression) 
BOTTOMSUM( TM1SubsetToSet([model].[model] , "_SAll N" , "public") , 1000 , [SalesCube].([actvsbud].[Actual], [region].[Argentina], [account1].[Units], [Month].[1 Quarter])) Returns all members where the sum of units is 1000 or less 
TopCount 
Sorts a set in descending order and returns the specified number of elements with the highest values. 
TopCount(<Set>,Count [ ,Numeric_Expression ] ) 
TOPCOUNT( TM1SubsetToSet([model].[model] , "_SAll N" , "public") , 10.0 , [SalesCube].([actvsbud].[Actual], [region].[Argentina], [account1].[Units], [Month].[1 Quarter])) Sorts and returns top 10 models based on units sold (SData) 
TopPercent 
Sorts a set in descending order, and returns a set of tuples with the highest values whose cumulative total is equal to or greater than a specified percentage. 
TopPercent(<Set>, Percentage, Numeric_Expression) 
TOPPERCENT( TM1SubsetToSet([model].[model] , "_SAll N" , "public") , 10 , [SalesCube].([actvsbud].[Actual], [region].[Argentina], [account1].[Units], [Month].[1 Quarter])) Returns models where the aggregate value contributes 10% of the total for all models 
TopSum 
Sorts a set and returns the topmost elements whose cumulative total is at least a specified value. 
TopSum(<Set>, Value, Numeric_Expression) 
TOPSUM( TM1SubsetToSet([model].[model] , "_SAll N" , "public") , 40000000, [SalesCube].([actvsbud].[Actual], [region].[Argentina], [account1].[Units], [Month].[1 Quarter])) Returns members where the sum is at least 40,000,000  3 members returned as the first 2 members was below 40 mil and needed a 3rd member to take it over the threshold. 
DrilldownLevel 
Drills down the members of a set to one level below the lowest level represented in the set, or to one level below an optionally specified level of a member represented in the set. 
DrilldownLevel(<Set> [ , , Index ] ) DrilldownLevel(<Set> [ , Level_Expression ] ) 
DrilldownLevel( {[Period].[Quarters].Members}) Expands each member of quarters, only to 1 level, not recursive. Similarly DrilldownLevel({[Period].[2020.FY]}) expands the year showing the year and month.
DrilldownLevel({[Period].[2020.FY HQ]},,0) will drill into the first hierarchy of the expression. 
DrilldownLevelTop 
Drills down the topmost members of a set, at a specified level, to one level below. 
DrilldownLevelTop(<Set>, <count> [,[<level_expression>] [,[<numeric_expression>][,INCLUDE_CALC_MEMBERS]]]) 
DrilldownLevelTop( {[Period].[2020.FY HQ]}, 1, [FISCAL YEAR]) Will return 2020.FY HQ and the first half as we specified a count of 1. If we changed this to 2, would get the year and both Halves 
DrilldownLevelBottom 
Drills down the bottommost members of a set, at a specified level, to one level below. 
DrilldownLevelBottom(<Set>, Count [ , Level_Expression [ ,Numeric_Expression ] ] ) 
Example needed  seems to behave like DrilldownLevelTop 
DrilldownMember 
Drills down the members in a specified set that are present in a second specified set. 
DrillDownMember(<Set 1>, <Set 2> [,[<target_hierarchy>]] [,[RECURSIVE][,INCLUDE_CALC_MEMBERS]]) 
DrillDownMember({[Period].[Fiscal Periods].Children},{[Period].[2020.FY]}) Will return all Fiscal years and all periods for 2020.FY 
DrilldownMemberTop 
Drills down the members in a Set 1 that are present in Set 2. You can limit the result set to a specified number of members using the count parameter. 
DrillDownMemberTop(<Set 1>, <Set 2>, <Count> [,[<Numeric_Expression>] [,[<Hierarchy>]] [,[RECURSIVE][,INCLUDE_CALC_MEMBERS]]]) 
DrillDownMemberTop({[Period].[Fiscal Periods].Children},{[Period].[2020.FY]},5) Will return all Fiscal years and first 5 periods for 2020.FY 
DrillupLevel 
Drill members that are below a certain level up 
DrillupLevel(<Set>[, [level expression]) 
DrillUpLevel( {TM1SubsetAll([Period])}, [Halves]) Will return the top levels rolling down until Halves. All levels below Halves are removed i.e. Quarters and Leaves 
DrillupMember 
Returns members from Set 1 that are not descendants of members in Set 2 
DrillupMember(<Set 1>, <Set 2>) 
DrillUpMember( {Descendants([Period].[Fiscal Periods])}, {[Period].[2020.FY]}) Set 1 would have the Fiscal Periods total, each Fiscal Year and its Periods. Set 2 has the one Fiscal Year. The result is that for all Fiscal Years, all months are shown, except for those in 2020.FY, only the 2020.FY parent is shown as descendants are drilled up to this member. 
DrilldownMemberBottom 
Returns a specified number of members from Set 1 that exist in Set 2. Function can also be used to drill down on a hierarchy. Not sure if <hierarchy> is supported  not tested. 
DrillDownMemberBottom(<Set 1>, <Set 2>, <count> [,[<numeric_expresion>] [,[<hierarchy>]] [,[RECURSIVE][,INCLUDE_CALC_MEMBERS]]]) 
DrilldownMemberBottom( {[Period].[Fiscal Periods].Children}, {[Period].[2020.FY]},5) Returns the Fiscal Years 2020.FY, 2021.FY etc. but drills down 2020.FY showing the first 5 children. 
ToggleDrillState 
Per TM1ToggleDrillState 





DIMENSION, HIERARCHY and LEVEL FUNCTIONS

Dimension.Name 
Intrinsic DIMENSION_NAME 


Hierarchy.Name 
Intrinsic HIERARCHY_NAME 


Level.Name 
Intrinsic LEVEL_NAME 


* Used in conjunction with other functions to obtain information about the members of a dimension, hierarchy, or level. 




COMMENTING 
// 
Inline comments or own line 


 
Inline comments or own line 


/*…*/ 
Blocked comments 








NUMERIC FUNCTIONS 
LEN 
Returns the length of a string 

FILTER({TM1SUBSETALL( [Period] )}, LEN([Period].CurrentMember.Name)=4) 
VAL 
Returns the value of a string (Nulls evaluate to 0) 

FILTER( {TM1SUBSETALL( [Scenario] )}, VAL([Scenario].CurrentMember.Properties("Flag"))=1) 
CoalesceEmpty 
Converts an Empty but not Null value to a specified value 
CoalesceEmpty( <Numeric_Expression> [ ,<Replacement_Numeric_Expression>,...n] )


Aggregate 

Need examples using SELECT 
Avg 

Need examples using SELECT 
Count 


FILTER({TM1FILTERBYLEVEL({TM1SUBSETALL( [Period] )},0)}, COUNT({ Intersect({[Period].Members},{[Period].CurrentMember},ALL) })>5) Returns all members that appear in the hierarchy more than 5 times. This is due to multiple rollups like Quarters, YTD etc. Could be used to find members with multiple parents where they should only have one. 
Correlation 


Covariance 


CovarianceN 


DistinctCount 


LinRegIntercept 


LinRegPoint 


LinRegR2 


LinRegSlope 


LinRegVariance 


LookupCube 


Max 


Min 


Median 


Rank 


StdDev 
Same as StDev  Alias 


StDev 


StdDevP 
Same as StDevP  Alias 


StDevP 


Sum 


Var 


Variance 
Same as Var  Alias 


VarP 


VarianceP 
Same as VarP  Alias 





TM1 Specific MDX Functions 
https://www.ibm.com/docs/en/planninganalytics/2.0.0?topic=mfstm1specificmdxfunctions1

TM1Member 
Returns a member from a specified tuple/subset. Avoid using as PAW translates any subset with TM1Member to underlying code. 
TM1Member 
TM1Member([Period].[_SCurrent Recharge Quarter].ITEM(1),0 ), 
TM1FilterbyPattern 
Returns members matching a specified pattern. An asterisk is used for any characters before or after, a question mark to indicate a particular position only. 
TM1FILTERBYPATTERN( <set>, <pattern_str> )
Undocumented is the 3rd parameter for an attribute:
TM1FILTERBYPATTERN( <set>, <pattern_str>[, <attribute>] )

{TM1FILTERBYPATTERN( {TM1SUBSETALL( [Period] )}, "*JUN*")} Returns all members containing JUN e.g. 2020JUN, 2021JUN
{TM1FILTERBYPATTERN( {TM1SUBSETALL( [Period] )}, "202?JUN")} Returns all Jun members in the 2020's, excludes 2019JUN 
TM1FilterByLevel 
Returns members matching a level number (0=Leaf) 
TM1FILTERBYLEVEL( <set>, <level_number>) 
{TM1FILTERBYLEVEL( {TM1SUBSETALL( [Period] )}, 0)} Returns all Leaf elements i.e. those at the lowest level. May include String and Numeric, not only Numeric. 
TM1DrillDownMember 
Drills down a member like the expand button in TM1 but for each Member in Set 2. Seems to behave exactly like DrillDownMember above. 
TM1DRILLDOWNMEMBER( <set1>, <set2>ALL [,RECURSIVE] ) 
TM1DrillDownMember({[Period].[Fiscal Periods].Children},{[Period].[2020.FY]}) Will return all Fiscal years and all periods for 2020.FY 
TM1Sort 
Sorts a set alphabetically, ignores hierarchies 
TM1SORT( <set>, ASCDESC ) 
TM1SORT( {TM1SUBSETALL( [Period] )}, ASC)} Will sort all elements alphabetically returning a mix of N and C levels 
TM1SortByIndex 
Sorts a set by index/ordinal 
TM1SORTBYINDEX( <set>, ASCDESC ) 
TM1SORTINDEX( {TM1SUBSETALL( [Period] )}, ASC)} Will sort all elements based on their ordinal position or Index in the hierarchy 
TM1SubsetAll 
Returns all Elements in a dimension, distinct, not all members. Where an element appears in multiple rollups, only the first occurrence is returned, like using DISTINCT() 
TM1SUBSETALL([<dimname>]) 
{TM1SUBSETALL( [Period] )} Will return all elements from Period without duplicates. 
TM1SubsetToSet 
Inserts the elements of a set into the current set 
TM1SubsetToSet(<Dimension>,<Hierarchy>,<SubsetName>[,<PublicPrivate]) 
TM1SubsetToSet([Period], "_SAll N", "public") Returns all the members from the underlying public subset. 
TM1TupleSize 
A count of members in the set/tuple 
TM1TupleSize 

TM1SubsetBasis 
A placeholder generated by the MDX recorded in Architect/Perspectives. Replace with a valid tuple. 

Not giving an example  if you see it, fix your code. 
TM1RollUp 
Rollup members from Set 1 to their parents if specified in Set 2 
TM1Rollup(<Set 1>, <Set 2>) 
TM1ROLLUP({[Period].[2020.FY].Children},{[Period].[2020FEB],[Period].[2020MAR]}) Will return all months and then roll up to the parents of 2020FEB and 2020MAR 
TM1ToggleDrillState 
Per MS MDX  Toggles the drill state of members between drilldown and drillllup modes. Need a better example on this. 
TM1TOGGLEDRILLSTATE(<Set 1>,<Set 2>,EXPAND_ABOVEEXPAND_BELOW,RECURSIVE) ) 
TM1TOGGLEDRILLSTATE( {[Period].[FISCAL PERIODS].Children}, {DESCENDANTS([Period].[2021.FY])}, EXPAND_BELOW, RECURSIVE ) 
TM1ToggleExpandMode 
Used to Expand Above or Below 
{TM1ToggleExpandMode(<set>,EXPAND_ABOVEEXPAND_BELOW)} 
{TM1ToggleExpandMode({Descendants([Period].[2021.FY])},EXPAND_ABOVE)} 



MDX VIEW RELATED KEYWORDS

With 
WITH MEMBER... 


Member 


As 


On 


Select 


From 


Rows 


Columns 


Pages 


Sections 


Chapters 


NonEmpty 
Returns the set of tuples that are not empty from a specified set, based on the cross product of the specified set with a second set. 
NONEMPTY(<Set>1 [,set_expression2]) 

CrossJoin 
It returns a set of tuples representing the cartesian product of the sets passed into it as parameters. Enables you to create 'nested' or 'crosstabbed' axes in queries, same as using * 
Crossjoin(<Set>1 ,Set_Expression2 [,...n] ) 

NonEmptyCrossJoin 
Deprecated  Do not use  Use Non Empty 


Where 


Solve_Order 


Format_String 


Clear 
CLEAR CALCULATIONS 


RollupChildren 


TM1Ignore_BadTuples 


TM1Format_String 


TM1RuleDerived 


TM1Subset_Ident 


TM1Update 


TM1Updateable 


TM1BangPrefix 

