Member & Set Expressions

Member Expressions

A member expression starts from a single member in a dimension and optionally uses various methods to derive a set of members from the same dimension. Derivation methods are appended using the dot operator:

dimension.member_spec[.derived_method[(arg)]...]

Member specification can be:

A member name
A member key prefixed by &
A member tag prefixed by #
The special keyword CurrentMember
KeyToMember(expr) / StrToMember(expr) / StrToName(expr) / StrToKey(expr) / StrToTag(expr) — resolves the member from a runtime expression instead of a literal. See Resolving a Member from an Expression below

note

Members cannot be referenced by Caption.

Examples:

[Account].[Rent]
[Account].#[netincome].[SelfAndDescendants]
[Period].CurrentMember.Ancestor(2).FirstLeafDescendant
[Department].&[23].Siblings
[Period].CurrentMember.SelfAndNext(11)

CurrentMember references (known as relative references) are only supported in assignments.

Note on Identifiers

Square brackets are used to enclose identifiers that start with or contain special characters. Anything in brackets will always be considered an identifier and never a keyword.

Therefore, [Account].CurrentMember and [Account].[CurrentMember] are two different things:

The former refers to the current member in the Account dimension
The latter refers to the account named "CurrentMember"

Best Practice

Enclose all dimensions, keys, names, and tags in brackets, and leave functions/methods without brackets.

Supported Methods

Method	Relative Supported	Description
`Ancestor(n)`	✅	Returns the ancestor n levels up. Nothing if member's level is < n
`Children`	✅	Returns all children of the member
`Descendants`	✅	Returns all descendants of the member (excluding the member)
`Filter(property, op, value)`	✅	Filters a set to members whose `property` matches `value` under operator `op`. See Filtering by Property below
`First`	✅	Returns the first member of a set in natural dimension order
`FirstLeafDescendant`	✅	Returns the first leaf descendant of the member (including the member itself)
`Lag(n)`	✅	Returns the member n places before the member, on the same level. `Lag(1)` is equivalent to `PrevMember`
`Last`	✅	Returns the last member of a set in natural dimension order
`LastLeafDescendant`	✅	Returns the last leaf descendant of the member (including the member itself)
`LeafDescendants`	✅	Returns all leaf descendants of the member. If the member is a leaf, returns the member
`Lead(n)`	✅	Returns the member n places after the member, on the same level. `Lead(1)` is equivalent to `NextMember`
`Next(n)`	❌	Returns at most n members on the same level as the referenced member, excluding the member itself
`NextMember`	✅	Returns the next member on the same level of the hierarchy. Equivalent to `Next(1)`
`Parent`	✅	Returns the parent member. Will return the virtual root for level 1 members. Equivalent to `Ancestor(1)`
`Prev(n)`	❌	Returns the previous at most n members on the same level as the referenced member, excluding the member itself
`PrevMember`	✅	Returns the previous member on the same level of the hierarchy. Equivalent to `Prev(1)`
`SelfAndChildren`	✅	Returns the member and its children
`SelfAndDescendants`	✅	Returns the member and its descendants
`SelfAndNext(n)`	❌	Like `Next(n)` but includes the referenced member. Referenced member is included in the count
`SelfAndPrev(n)`	❌	Like `Prev(n)` but includes the referenced member. Referenced member is included in the count
`Siblings`	✅	Returns all children of the parent member, including the referenced member

For methods that support arguments, the argument must be an integer constant—it cannot be an expression. As an exception, Ancestor() also supports a parameter as the argument.

Filtering by Property

Filter(property, op, value) narrows a set to members whose dimension property satisfies the given condition:

[Scenario].Members.Filter("IsActive", "=", 1)

All three arguments must be literals — property and op are string literals, value is a string or number literal. None of them can be an expression, a parameter, or a variable.

property must be a property declared on the dimension being filtered.
op must be one of =, <>, <, <=, >, >=.
value must be valid for the property's declared type.

Ordering operators are not allowed against a member-reference property. A property that stores a member key (e.g. a Scenario's EndPeriod property, which stores a Period key) can only be compared with = or <> — key order is not the same as chronological or hierarchical order, so Filter("EndPeriod", "<=", ...) is rejected at compile time. To compare positions within a hierarchy, use range() instead.

Resolving a Member from an Expression

A literal member name, &[key], and #[tag]# are all fixed at the time the script is written. StrToMember(expr), StrToName(expr), StrToKey(expr), and StrToTag(expr) instead resolve the member from expr, a scalar expression evaluated at runtime — it can use parameters, string functions, concatenation, and operators, not just a constant:

param @region

scope ([Account].StrToMember(concat("Rent_", @region)))
  [New] = 0
end scope

Calling this script with @region = "East" resolves to the account named Rent_East; @region = "West" resolves to Rent_West — the same compiled script serves any region without recompiling.

The functions differ only in which column they match against:

Function	Matches against
`KeyToMember(expr)`	Key (same as `StrToKey`)
`StrToMember(expr)`	Name (same as `StrToName`)
`StrToName(expr)`	Name
`StrToKey(expr)`	Key
`StrToTag(expr)`	Tag

They can be used in scope(...)/freeze(...) and in assignments, on their own or as a tuple member. In an assignment, expr can also read property values of the cell's current members, so the member can change from cell to cell:

scope ([Account].[Local Amount USD], [Scenario].[Actual], [Period].[2025].LeafDescendants)
  [New] = [Account].[Local Amount]
            * ([Account].[FX Rate], [Currency].KeyToMember([Entity].CurrentMember.Property("Currency")))
end scope

A key or name that matches no member makes the reference empty.

In scope(...)/freeze(...), expr can read property values of named members or of @parameter members (a scope is not evaluated per cell, so CurrentMember has nothing to read). This lets a scope follow a member-reference property such as a scenario's End Period:

param @ScenarioKey

-- formula A up to the scenario's end period, formula B after it
scope ([Scenario].&[@ScenarioKey],
       range([Period].[Jan 2025], [Period].KeyToMember([Scenario].&[@ScenarioKey].Property("EndPeriod"))))
  [New] = <formula A>
end scope
scope ([Scenario].&[@ScenarioKey],
       except([Period].[2025].LeafDescendants,
              range([Period].[Jan 2025], [Period].KeyToMember([Scenario].&[@ScenarioKey].Property("EndPeriod")))))
  [New] = <formula B>
end scope

Run the script once per scenario (e.g. from a workflow foreach). A scenario without an end period gets an empty range(), so only formula B applies. To handle every scenario in a single run instead, compare the cutoff per cell in the assignment: iif([Period].CurrentMember.Property("EndDate") <= [Period].KeyToMember([Scenario].CurrentMember.Property("EndPeriod")).Property("EndDate"), <formula A>, <formula B>).

Limitations:

Must appear after a dimension prefix, e.g. [Account].StrToMember(expr) — a standalone form without a dimension prefix is not currently supported.
Inside a tuple, expr cannot read the current member of a dimension that the same tuple fixes.
Because the value is only known at runtime, these do not benefit from the compile-time optimization applied to single, literal-member scopes.

Property Values

.Property("Name") returns the value of a dimension property for a member, and can be used in assignment expressions. It can be applied to the current member, a fixed member, a KeyToMember() member, or a member reached through single-member methods (Parent, Ancestor(n), PrevMember, NextMember, Lag(n), Lead(n)):

scope ([Account].[Bonus], [Scenario].[Plan], [Department].[All].LeafDescendants)
  [New] = [Account].[Salaries] * [Department].CurrentMember.Property("BonusRate")
            + iif([Department].CurrentMember.Parent.Property("Region") = "EMEA", 1000, 0)
end scope

A property that stores a member reference returns the member key: use it with KeyToMember(), or compare it with = / <>.
A member without a value for the property gives an empty result.
A property value cannot be used as a tuple member; wrap it in KeyToMember() to use it as a member.

To select members by property value in scope/freeze, use Filter().

Set Expressions

Set expressions combine multiple member expressions using set operators:

Function	Description
`Union(set1, set2[, setn...])`	Performs a union between all sets; eliminates duplicate members
`Intersect(set1, set2[, setn...])`	Performs an intersection between all sets; eliminates duplicate members
`Except(set1, set2[, setn...])`	Discards from set1 all members that exist in set2 through setn
`Range(set1, set2)`	Returns all members between set1 and set2. Both arguments must be single-member sets that are on the same level in the hierarchy

note

Set expressions are supported only in Scope and Freeze statements.

Member Expressions​

Note on Identifiers​

Supported Methods​

Filtering by Property​

Resolving a Member from an Expression​

Property Values​

Set Expressions​