Home > MDX Challenges > MDX #44–How to find all the calculated measures

MDX #44–How to find all the calculated measures

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.

pic1

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.

pic2

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The following screenshot shows the results.

pic3

 

 

 

 

 

 

 

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.

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: