Document toolboxDocument toolbox

Support center for flex.bi version 3.2

Calculated members (MDX)

Owned by Juris Aidma (Deactivated)
Last updated: Sept 09, 2016 by Gvido
15 min read

If you have not yet introduced yourself to the basic concepts of flex.bi then please start with an overview of flex.bi concepts and learn how to analyse and create reports. This tutorial will explain how to define new calculated members using MDX calculation formulas.

If you have never worked with MDX before, we suggest you watch this video: MDX basic concepts 

Dimensions and hierarchies

Each cube dimension contains one or more hierarchies with one or more hierarchy levels. And each hierarchy level contains dimension members. There are normal members which are imported from source files or applications as well as you can define calculated members using calculation formulas. Calculation formulas are defined using MDX query language.

There is special dimension Measures which contains all imported measures. Most frequently calculated members are defined in Measures dimension and then they are typically called as calculated measures.

Define new calculated member

If you would like to define new calculated members then expand the corresponding dimension (e.g. Measures) and expand the Calculated members section:

If you would like to define a new calculated member then click on the "Define new" link, if you would like to edit (or delete) existing calculated member then click on the "edit" link. If you do not have rights to edit calculated members in the current account then you will see a "show" link instead of an "edit' link. Without the option the edit a calculated member you can only see the definition of existing calculated members. When you define a new member or edit an existing calculated member you will see the following calculated member definition form:

Each calculated member should have a name (unique within dimension) and a calculation formula (the rest of tutorial will teach how to write calculation formulas). In addition you can specify how the calculated member values should be formatted (e.g. as integer, decimal, date or using default formatting). From the right sidebar you can quickly select other members, dimensions, operators or functions to insert them into the calculation formula.
After defining the calculated member formula press Update to save your work or Delete to delete the currently opened calculated member or Cancel to discard any changes and close the window. If the calculated member formula is invalid then a corresponding error message will be displayed.

Do not modify or delete pre-defined calculated members that were created by the source application import - their original definition will be recreated next time you will perform the source application import. If you need to create a modified version of existing pre-defined calculated member then copy its calculation formula and create a new calculated member with a different name and modify copied calculation formula for the new calculated member. 

Let's start writing some calculation formulas! The following examples will use data from the Sales demo cube dimensions and measures to illustrate how to write the calculation formulas.

Dimension, hierarchy, level and member names

When writing calculation formulas you will need to reference dimensions, dimension hierarchies, hierarchy levels and other existing dimension members. In the MDX query language all names are enclosed in square brackets [ ].

 To reference a dimension you just enclose its name in square brackets, e.g. [Customers], [Time] or [Measures].

 If a dimension has just one hierarchy then you can reference the primary hierarchy in the same way as a dimension, e.g. [Customers] or [Measures]. When you import the Time dimension then it will automatically create the main hierarchy (year, quarter, month and day levels) as well as a Weekly hierarchy (year, week and day levels). Time will reference the Time dimensions main hierarchy but Time.Weekly will reference the Time dimensions Weekly hierarchy.

 When you expand the All hierarchy level members then you see names of all the main hierarchy levels. You can reference a particular hierarchy level with [dimension or hierarchy name].[level name]. E.g. [Customers].[City] references the City level of the main hierarchy of the Customers dimension.

 When referencing the Time dimension use [Time].[Year], [Time].[Quarter], [Time].[Month], [Time].[Day] to reference the main hierarchy levels and [Time].[Weekly].[Year], [Time].[Weekly].[Week], [Time].[Weekly].[Day] to reference the weekly hierarchy levels.
Each dimension typically will have a default All member which can be used to get totals of measures per this dimension. If the Customers dimension has a default All member named a All Customers then you can reference it with [Customers].[All Customers]. Since the default All member can be renamed it is safer to use [Customers].[DefaultMember] to get the same result. The time dimension has two default All members for each hierarchy - [Time].[DefaultMember] and [Time].[Weekly].DefaultMember.

 When you want to reference the top level dimension members then you have to use the following format - [dimension or hierarchy name].[member name]. E.g. [Customers].[USA] will reference the USA member from the top level of the Country level. To reference detailed level dimension members* you need to specify the full "hierarchy path" to this member, e.g. [Customers].[USA].[CA] to reference the CA member in the State Province level under the USA parent member. Or [Customers].[USA].[CA].[San Francisco] to reference a city etc.

 All measures are in the top level of the Measures dimension and you can reference them with e.g. [Measures].[Store Sales], [Measures].[Store Cost] etc.

Simple arithmetic calculations

Now that you know how to reference other existing measures and other dimension members we can create simple arithmetic calculations.
If we have the [Measures].[Store Sales] and [Measures].[Store Cost] measures then we can define a new calculated measure - Profit (which will have the full name [Measures].[Profit]) with the formula: [Measures].[Store Sales] - [Measures].[Store Cost]

When you use the new Profit measure in your flex.bi reports then this formula will calculate the difference between the Store Sales measures value and the Store Cost measures value.

 You can also use calculated measures in other calculated measures (it is important to define them in the right sequence - you can only use existing calculated members). For example, you can now define [Measures].[Margin %] with the formula [Measures].[Profit] / [Measures].[Store Sales] and it will calculate the margin as a number from 0 to 1. If you would like to display the results as a percentage then change the Formatting of this calculated measure to use integer or decimal percentage formatting.

 You can also perform arithmetic calculations for calculated members in other dimension. For example, you could define [Customers].[West coast] calculated member in the Customers dimension with the formula [Customers].[USA].[CA] + [Customers].[USA].[OR] + [Customers].[USA].[WA] and now when you combine the calculated members in reports [Customers].[West coast] with [Measures].[Store Sales] you will get the total sales for all these three states together. If you combine it with [Measures].[Profit] you will get the total profit for all these three states.

Moving across dimension levels

When you use [Measures].[Store Sales] it will calculate the value of the Store Sales measure for other corresponding dimension members for each row / column in report. If you would like to override some other dimension value in your calculation formula then you need to use tuples. Tuples are a combination of members from different dimensions. The MDX syntax for tuples is (member_1, member_2, ..., member_n). For example, if you would like to get the Store Sales measure value for all customers then you would use the tuple ([Measures].[Store Sales], [Customers].DefaultMember.

 Let's define a calculated measure [Measures].[Percent] with the formula (and percentage formatting) [Measures].[Store Sales] / ([Measures].[Store Sales], [Customers].[DefaultMember]).

 When you combine [Measures].[Percent] with e.g. [Customers].[USA].[CA] then you will see the percentage of California sales from total customer sales in the report.

 Maybe you don't want to see the percentage from total sales, but the percentage from the customer hierarchies direct parent sales (e.g. for the city level show the percentage from corresponding state sales). In this case you can use the CurrentMember dimension or hierarchy property to access to the current member of a corresponding dimension for which the expression is currently evaluated. E.g. [Customers].CurrentMember will return the current member in the Customers hierarchy for which this expression is evaluated. Or in other words if the Customers dimension is placed in rows section then by using [Customers].CurrentMember we know for which row this formula is currently being evaluated.

 If we have a current member then we can navigate to other dimension members relative from this member. [Customers].CurrentMember.Parent will return parent member in the Customers hierarchy for the current member. Ancestor([Customers].CurrentMember, [Customers].[Country]) will move from current member up to "ancestor" in the Country level.

 So we can define the calculated measures [Measures].[Percent] as [Measures].[Store Sales] / ([Measures].[Store Sales], [Customers].CurrentMember.Parent)

Moving in time

Just like you can move to different dimension hierarchy levels you can also move to different dimension members in the same dimension. It is typically used in the Time dimension when you want to compare measures between different time periods or aggregate time period range.

For example, the calculated measure [Measures].[Sales monthly growth] with the  formula [Measures].[Store Sales] - ([Measures].[Store Sales], [Time].CurrentMember.PrevMember) will calculate the [Measures].[Store Sales] growth comparing to the previous Time dimension members period. If this calculated measure is combined in a report with a month in the Time dimension it will show growth comparing to the previous month, if it is combined with a year then it will show growth comparing to the previous year.

There are several functions that help you "move in time":

  • [Time].CurrentMember.PrevMember returns the previous member in the same hierarchy level (it will return an empty member for the first member)
  • [Time].CurrentMember.NextMember will return the next member
  • [Time].CurrentMember.Lag(2) will return the previous member with a distance of 2 (use any number for argument, Lag(1) is the same as PrevMember)
  • [Time].CurrentMember.Lead(2) will return the next member with a distance 2 (Lead(1) is the same as NextMember)
  • ParallelPeriod([Time].[Year], 1, [Time].CurrentMember) will return a "parallel" Time member one year ago (e.g. for a day level member Jan 01 2012 it will be Jan 01 2011 but for a month level member Jan 2012 it will be Jan 2011)
  • OpeningPeriod([Time].[Day], [Time].CurrentMember) will return the first descendant of the current Time member at the Day level (first day of the year, quarter or month which is defined in the Time dimension)
  • ClosingPeriod([Time].[Day], [Time].CurrentMember) will return the last descendant of the current Time member at the Day level (last day of the year, quarter or month which is defined in the Time dimension). Sometimes you would like to test if your time navigation expression is working as you expect (before using it in further calculations), use the .Name member property to get the member name as the calculation formula result. For example, define a new calculated measure [Measures].[test opening day] with the formula OpeningPeriod([Time].[Day], [Time].CurrentMember). Name and use it in a report together with the Time dimension members and see if you get the expected result for the Time dimension members at different levels.

Sets

Now you know how to navigate to individual dimension members. But quite often you need to perform operations on a set of dimension members, for example, find a sum or average value of some measure over a selected set of dimension members.

The easiest way how to use sets in calculation formulas is to use a list of members enclosed in curly braces, e.g.{[Customers].[USA].[CA], [Customers].[USA].[OR], [Customers].[USA].[WA]}
{Time.2011, Time.2012}

If you would like to select a set as a range of sequential dimension level members then you can specify the first and the last member and use ":" between them. For example, this will create a set of dates from Jan 01 2012 to Jan 15 2012 then formula would look like [Time].[2012].[Q1 2012].[Jan 2012].[Jan 01 2012]:[Time].[2012].[Q1 2012].[Jan 2012].[Jan 15 2012]

Quite often you don't want to specify the exact range of members but would like to get all the dimension hierarchy level members. You can do it with the Members method, for example, to get all months in Time dimension create a calculated member with the formula - [Time].[Month].Members

When you want to test which members will be returned by some set expression then you can use SetToStr functions to create a string of concatenated member full names. For example, define the calculated measure [Measures].[test all months] with formula the SetToStr([Time].[Month].Members)

There are several other useful functions for working with sets, here are some examples how to use them:

  • [Customers].[USA].[CA].Children | returns a set of child members using the dimensional hierarchy (in this example all cities in California)
  • Descendants([Customers].[USA], [Customers].[City]) | returns a set of member descendants at a specified hierarchy level (in this example all cities in USA)
  • [Customers].[USA].[CA].[San Francisco].Siblings | returns all members which have the same parent as this member (in this example all cities in California), it is the same as using [Customers].[USA].[CA].[San Francisco].Parent.Children .There are additional methods that you can use to get just the first or last members of these sets - FirstChild, LastChild, FirstSibling, LastSibling.

Sets of time periods

There are additional useful set selection methods for Time periods

  • PeriodsToDate([Time].[Year], [Time].CurrentMember) returns all periods from the beginning of the current member in the specified level (in this example from beginning of the current year) until the specified member.
  • YTD([Time].CurrentMember) is a shorter version of the same function (abbreviation from Year-to-date)
  • QTD([Time].CurrentMember) returns a quarter-to-date set of members
  • MTD([Time].CurrentMember) returns a month-to-date set of members
  • WTD([Time].CurrentMember) returns a week-to-date set of members. If you only have the time dimension in your cube then you can use even shorter expressions YTD(), QTD(), MTD() and WTD(). By default they will receive the current member of the time dimension [Time].CurrentMember as an argument. But sometimes you need to pass different arguments to these functions. For example, if you would like to compare the current year-to-date aggregates with year-to-date aggregates from a year ago you can use the following expression to get a year-to-date set for the corresponding Time dimension members a year ago: YTD(ParallelPeriod([Time].[Year], 1, [Time].CurrentMember))
    And if you like shorter expressions, you can omit the current member argument here as well YTD(ParallelPeriod([Time].[Year], 1)). Most of time related functions will use [Time].CurrentMember as the default member argument.
    If you want to get a number of last periods from the Time dimension then you can use the LastPeriods function. For example - LastPeriods(3, [Time].CurrentMember)
    will return a set with the current Time member and two of its previous members. If you would like to get the current member and its next two Time period members then use "-3" as the value.

Set operations

There are several operations that you can perform on sets:

  • {set1, set2, ..., setn} returns a union of two or more sets
  • Except(set1, set2) returns set1 members but removes any member that is in set2
  • Head(set, number) returns a set with the number of members specified by the given number from original set (if the given number is not specified then the first member from the set is returned)
  • Tail(set, number) returns the last number of members from a set
  • set.Item(position) returns one member from a set with a specified position (starting from zero). So if you would like to get the first member of set you can use the expression Head(set).Item(0). Quite frequently you would like to filter the set members using some condition. You can do this with Filter(set, condition). For example in this way you can filter all cities with sales larger than a 1000 - Filter([Customers].[City].Members, [Measures].[Store Sales] > 1000). 
  • The conditional expression [Customers].CurrentMember references the current set member for which the condition is evaluated. For example, this will return all cities whose name starts with San (by using the MATCHES operator with a regular expression) - Filter([Customers].[City].Members, [Customers].CurrentMember.Name MATCHES 'San .*') 
  • Another typical function that is used in conditions is the IsEmpty function. This expression will return all cities which have non-empty sales amounts - Filter([Customers].[City].Members, NOT IsEmpty([Measures].[Store Sales])) 
  • Previously the simple set to string function SetToStr was mentioned because it is useful for expression testing purposes. But if you would like to format the set results in a customised way then you can use the function Generate(set, string_expression, separator_string). For example the following expression will return city names concatenated using a comma where there is no sales amount recorded - Generate(Filter(Customers.City.Members, IsEmpty(Measures.Store Sales)), Customers.CurrentMember.Name, ', ')

Aggregates

Now that you know how to select different sets of dimension members you can calculate different aggregated values from these sets:

  • Sum(set, numeric_expression) calculates numeric_expression for each set member and returns sum of all these results. For example, Sum(LastPeriods(3). [Measures].[Store Sales]) will calculate total sales for last three Time periods starting from current Time dimension member.
  • Count(set) returns count of set members. Count(set, ExcludeEmpty) will return count of set members for which corresponding measure values are not empty.
  • Avg(set, numeric_expression) calculates the average of numeric_expression in a set
  • Max(set, numeric_expression) returns the maximum value of numeric_expression in a set
  • Min(set, numeric_expression) returns the minimum value of numeric_expression in a set

Conditions

You can write formulas with conditions and different results based on condition evaluation using IIF and CASE functions. For simple IF / THEN / ELSE type of conditions use IIF(condition, if_true_expression, if_false_expression) (notice that there are two I letters in IIF), for example

IIF([Measures].[Profit] > 0, 'Profit', 'Loss')


If there are many conditions then it is easier to use CASE function, for example

CASE 
WHEN [Measures].[Profit] > 1000 
THEN 'Big profit' 
WHEN [Measures].[Profit] > 0 
THEN 'Small profit' 
ELSE 'Loss' 
END


If all conditions are comparisons of the same expression to different expected values then another CASE form can be used, for example

CASE Time.CurrentMember.Level.Name 
WHEN 'Month' 
THEN Sum(LastPeriods(3), [Measures].[Store Sales]) 
WHEN 'Day' 
THEN Sum(LastPeriods(90), [Measures].[Store Sales]) 
END


In IIF and CASE conditions standard comparison operators can be used (=, <, <=, <>, >, >=) as well as AND, OR and NOT operators as well as several specific operators:

  • IS returns whether two objects are the same, for example

    [Customers].CurrentMember IS [Customers].DefaultMember

      (which will be true if Customers current member is default All Customers member)

  • IN and NOT IN returns whether member is in a set, for example

    [Customers].CurrentMember IN [Customers].[USA].[CA].Children

Member properties

Dimension members has some default properties (like .Name) as well as they can have additional custom properties. In flex.bi the source application import (e.g. from Basecamp, Highrise or JIRA) are also importing additional dimension fields from source systems. MDX has a standard Properties function to access member properties but flex.bi defines an improved getProperty function (which will return an empty result instead of an exception if no property is defined for current dimension level).

 For example, Highrise import adds Created date property for all imported deals. The following expression returns Created date property value for Deals dimensions current member - [Deals].CurrentMember.getProperty('Created date')

Date type conversion

Sometimes you might need to convert a string expression to an integer, decimal or date expression value (for example, to convert Highrise custom field string value to corresponding type to be able to use it in further calculations). There are several functions available for data type conversions:

  • CInt(value) returns a value converted to integer
  • CDbl(value) returns a value converted to double floating number type (should be used when results need to be a decimal value)
  • flex.bi defines DateParse(value) which will try to convert a value to a date value using different date formats (for example both 2012-01-31 and Jan 31 2012 will be converted to a correct date). There is an MDX standard CDate function but it supports less date types.

Time difference calculations

flex.bi defines DateDiffDays(from_date, to_date) which will return the difference in days between two dates. It can be used together with the Now() function (which returns the current time) to get a distance in days between a selected time dimension member and the current date, for example

DateDiffDays(DateParse([Time].CurrentMember.Name), Now())

In addition, flex.bi defines the function DateAddDays(date, number_of_days) which will return a new date in the past (if number_of_days is negative) or in the future (if number_of_days is positive). For example, this will return a date which is 5 days from the current Time dimension member - [Date].DateAddDays(DateParse([Time].CurrentMember.Name), 5)

Sometimes it is useful to get the Time dimension member which corresponds to an actual current date. This can be done with the flex.bi specific dimension hierarchy level property CurrentDateMember. For example, these expressions will return the month of the current date and the week of the current date [Time].[Month].CurrentDateMember
 [Time].[Weekly].[Week].CurrentDateMember
If there is no Time dimension level that corresponds to the current date (e.g. if data for current month or week is not yet imported) then the CurrentDateMember function will return the last period before the current date which is present in the corresponding Time dimension level.

Default values

Sometimes you might want to return some default value if a measure or function returns an empty value. In these cases you can use the CoalesceEmpty(expression, default_value) function, for example - CoalesceEmpty([Measures].[Store Sales], 0)

Comments

It is possible to write comments in calculation formulas. Use comments either to describe some non-obvious complex calculations. Commenting is also valuable during the writing and testing of calculation formulas - when something is not working as expected then comment all of the formula lines and leave some fragments of the formula that you would like to debug.

-- one line comment
 

expression -- comment until end of line
 

/* multi line
comment
*/

List of all MDX functions

This tutorial covered the most frequently used MDX functions and examples on how to use them. If you didn't find what you need then take a look at the list of all MDX functions.
If you have any unclear questions or issues when writing the calculation formulas then contact flex.bi support.

Creating dashboards

Learn how to create dashboards in which to display your reports here