Calculated measures are visually identifiable

If you have access to a cube, it’s quite easy to find all the calculated measures.

The following is a screenshot from SSMS MDX query editor. The icon next to each measure can visually tell you if it’s a regular measure or a calculated measure. The icon with three uneven bars indicates a regular measure, and the one with a calculator and an orange bar (not sure what it represents) on top indicates a calculated measure.

AllMembers function returns both the regular and calculated measures

I am always interested in knowing how to also accomplish things with MDX queries.

It turned out it’s pretty simple. The key is the AllMembers function.

By default, MDX excludes calculated members when it resolves set functions. In this blog, I am only examining the special dimension, Measures, which has only one single visible hierarchy; as a result, the hierarchy can be either referred to by the dimension name or by the hierarchy name. So both of the following are valid expressions, and both will return regular measures only.

[Measures].[Measures].Members

[Measures].Members

The AllMembers function is very straightforward. It will return what it is meant to do, both the regular measures and the calculated measures.

Here are 4 simple statements regarding how to use AllMembers function to get calculated measures.

1. Members function only returns regular members, or regular measures on the Measures hierarchy.
2. AllMembers function returns regular members AND calculated members, or calculated measures on the Measures hierarchy.
3. (AllMembers – Members) gives us calculated measures only.
4. AddCalculatedMembers() function is semantically similar to the AllMembers function.

The following are 4 MDX queries to demonstrate the 4 statements above.

The following screenshot shows the results.

Here are the queries in text.

–1. Members function only returns regular members, or regular measures on the Measures hierarchy

SELECT

{[Measures].Members} ON COLUMNS

FROM

[Adventure Works]

go

–2. AllMembers function returns regular members AND calculated members, or calculated measures on the Measures hierarchy

SELECT

{[Measures].AllMembers} ON COLUMNS

FROM

[Adventure Works]

go

–3. AllMembers – Members gives us calculated measures only

SELECT

{ [Measures].AllMembers –

[Measures].Members

} ON COLUMNS

FROM

[Adventure Works]

go

–4. AddCalculatedMembers() function is the same as the AllMembers function

SELECT

AddCalculatedMembers([Measures].Members) –

[Measures].Members

ON COLUMNS

FROM [Adventure Works]

The AllMembers function and the AddCalculatedMembers() function can be also applied to other regular dimensions to get the calculated members.