Calculated measures and members

On this page:

What is a calculated member and when to use calculated members

This tutorial explains how to define new calculated members and measures using MDX expressions (or formulas). If those words do not make sense to you, we advise getting to know the main eazyBI concepts and learn how to create reports and then come back to this section again.

Calculated members and calculated measures can be used to add business logic to a report and a data cube. Most common use cases for calculated members and measures are to group particular items of interest, dynamically change time according to the current date or selected period and, most importantly, derive new measurements from existing data. Calculated members and measures are defined using the MDX (multidimensional expressions) query language which is the main query language implemented by Mondrian (https://mondrian.pentaho.com/documentation/mdx.php)


What you should know

Mastering how to write MDX expressions is initially quite difficult. Don't worry, you will get along quite well if you will follow some ground rules. MDX is a language and to use it for calculations it is enough to understand some base words (data types and functions) and how to use a dictionary (list of available functions)

Functions

A function is a mathematical or logical action which transforms data and then returns a modified version of data. In other words, some data goes IN, then function transforms them, and another data comes OUT.

You can recognize function by the red color of the text and symbols defining the start and end of the function, brackets (), curly brackets {}, dot .

For example, function DateDiffDays() calculates how many days are between two given dates. In this function goes IN two dates and comes OUT one number representing a count of days.

Data types

A data type is a particular kind of data item, as defined by the values it can take. In MDX you can use the following data types:

Number42, 91.6%, 17h 38m
String"Guns N' Roses", "679864278"
DateMay 25 2018
Set - a collection of distinct objects{%,&,@,*,$,!}
Member - something you can get as one row[Time].[Year].[2018], [Region].[Continent].[Country].CurrentMember
Boolean - logical yes or notrue, false

Ground rules

  • Calculations which refer to members from several dimensions or numerical measures should be created as new user-defined measures. We call them calculated measures. (LINK TO DO).
  • A measure binds data together and allows you to represent data from different points of view (Dimensions). Therefore, each calculated measure should contain at least one already existing measure or fixed value. Masure is a treasure!
  • When creating a calculated member in a dimension, use only members from one dimension where you create them. Don't mix dimensions in calculated members! We recommend using an aggregate function for calculated members and do not perform arithmetical operations, like, multiplying, dividing and subtracting in those calculations (TODO LINK)
  • Use tuples when possible. (TODO LINK).
  • Start simple and build calculated members and measures step by step adding complexity gradually.

Naming conventions

Each dimension [ 1 ] contains one or more hierarchies with one or more hierarchy levels [ 2 ], and each hierarchy level contains dimension members. When creating new calculated measures and members, we usually refer to the specific members with which we want the code to work. So we should show the full path on how to find that particular member. One way to identify a member is to start with the name of the dimension and work downwards, specifying the members at each level in the hierarchy [ 3 ] until we reach the required member.

For example, the Time dimension contains three hierarchies: default, Weekly and Fiscal. Going further down the default hierarchy it has five levels: All Times (default level), year, quarter, month, day [ 2 ]. To represent a specific month of the year, the path to May 2019 would look like this:

[Time].[2019].[Q2 2019].[May 2019]

In MDX query language, all names of measures, dimensions, dimension hierarchies, hierarchy levels, and dimension member are enclosed in square brackets []. To reference a dimension, you just enclose its name in square brackets, e.g., [Customers], [Measures], [Time]To reference a particular member enclose each name of the path in square brackets, e.g., [Customers].[USA].[CA], [Time.Weekly].[2019].[W31, Jul 29 2019] or [Measures].[Store Sales].

  • If a dimension has just one hierarchy, it automatically is also the primary hierarchy. And you can refer to the primary hierarchy in the same way as a dimension by mentioning just a dimension name, e.g. [Customers] or [Measures].
  • If a dimension has two or more hierarchies (like Time dimension), then you should refer dimension name and hierarchy name. For example, a reference to  Time dimension Weekly hierarchy is [Time.Weekly]. In Time dimension primary hierarchy (year, quarter, month day) does not have a specific name, and you can refer to it by mentioning just a dimension name [Time].

Each dimension typically will have a default All member which can be used to get totals of measures by this dimension. If Customers dimension has a default All member named All Customers, then you can reference it with by its name [Customers].[All Customers] or [Customers].CurrentHierarchy.DefaultMember to get the same result. In Measures, all measures are at the top level, and you can reference them with as [Measures].[Store Sales], [Measures].[Store Cost] etc.

How to define a new calculated member

If you would like to define new calculated member then expand corresponding dimension (e.g. Measures) and expand 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) an 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 just the show link which will show the definition of an existing calculated member. When you define a new calculated 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 the tutorial will teach how to write calculation formulas). In addition, you can specify how calculated member value should be formatted (e.g. as an integer, decimal, date or using default formatting). From the right sidebar, you can quickly select other members, dimensions, operators or functions to insert them in the calculation formula.

After defining a calculated member formula press Update to save it or Delete to delete an existing calculated member or Cancel to discard any changes. If a calculated member formula will be invalid then a corresponding error message will be displayed.

Do not modify or delete pre-defined calculated members that were created by source application import – their original definition will be recreated next time when you will perform the source application import. If you need to create a modified version of an 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! Following examples will use Sales demo cube dimensions and measures to illustrate the creation of calculation formulas.

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 members that are in set2

  • Head(set, number) returns a set with the first number of members from the original set (if number is not specified then a set from the first set member is returned)

  • Tail(set, number) returns the last number of members from a set

  • set.Item(position) returns one member from a set with the specified position (starting from zero). So if you would like to get the first member of a set you can use the expression Head(set).Item(0)

Quite frequently you would like to filter 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 1000:

Filter([Customers].[City].Members, [Measures].[Store Sales] > 1000)

Within the condition expression [Customers].CurrentMember references the current set member for which the condition is evaluated. For example, this will return all cities which name starts with San (using MATCHES operator with regular expression)

Filter([Customers].[City].Members, [Customers].CurrentMember.Name MATCHES 'San .*')

Other typical function that is used in conditions is IsEmpty. This expression will return all cities which have non-empty sales amount:

Filter( [Customers].[City].Members, NOT IsEmpty([Measures].[Store Sales]) )

Previously a simple set to string function SetToStr was mentioned that is useful for expression testing purposes. But if you would like to format set results in a customized way then you can use 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, ', ' )

Member properties

Dimension members have some default properties (like .Name and .Key), as well as they, can have additional custom properties. eazyBI 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. eazyBI defines an additional get (and also with a longer name getProperty) function which will return an empty result instead of an exception if no property is defined for the current dimension level.

For example, Jira import adds the Created at the property for all imported issues. The following expression returns the Created at property value for the Issue dimension current member:

[Issue].CurrentMember.get('Created at')

Date type conversion

Sometimes you might need to convert string expression to an integer, a decimal or a date expression (for example, to convert Highrise custom field string value to the 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 an integer

  • CDbl(value) returns a value converted to a double floating number type (should be used when results should be decimal)

  • eazyBI defines an additional function DateParse(value) which will try to convert value to a date value using different date formats (for example both 2012-01-31 and Jan 31 2012 will be converted to the correct date). There is an MDX standard CDate function but it supports fewer date formats.

  • DateParse function also supports dynamic date expressions as offset from today (learn more about them in date filters help page). For example, you can use DateParse('today') or DateParse('30 days ago') or DateParse('1 week from now').

Default values

Sometimes you want to return a default value if some measure or function will return an empty value. In these cases you can use CoalesceEmpty(expression, default_value) function, for example:

CoalesceEmpty([Measures].[Store Sales], 0)

How to work with the formula editor

Comments

It is possible to write comments in calculation formulas. Use comments either to describe some non-obvious complex calculations or also commenting is valuable during debugging of the calculation formulas. When something is not working as expected then comment all formula lines and leave uncommented just some part of the formula that you would like to debug. You may also mark an area of formula and use shortcut ⌘ / to comment it.

-- one line comment expression -- comment until end of line /* multi line comment */

Special comments with annotations

Available on eazyBI Cloud or starting from version 4.6 of eazyBI add-on for Jira or Private eazyBI.

-- annotations.group=...
Put the calculated member in the specified group in the Calculated member's section of the dimension in the report builder.

-- annotations.disable_drill_into=true
Do not allow the Drill into action for this calculated member.

-- annotations.disable_drill_across=true 
Do not allow the Drill across action for this calculated member.

-- annotations.disable_drill_through=true 
Do not allow the Drill through action for this calculated member.

-- annotations.disable_drill=true 
Disable all drill actions for this calculated member.

 

Formatting 

When creating a new calculated measure you can choose the format for the output of results. 

If not selected, eazyBI would return a default format which sometimes is not correctly identified by eazyBI (for example, if calculation is the result of DateDiffDays function)

You can choose between the following formats:

  • #,###.## Decimal
  • #.### Integer
  • ##.##% Decimal percentage
  • ##% Integer percentage
  • mmm dd yyyy Month Day Year
  • mmm yyy Month Year
  • yyyy-mm-dd ISO format date
  • yyyy-mm-dd hh:mi:ss Date and time
  • ##d ##h ##m Days, hours, minutes
  • ##h ##m Minutes
  • custom

Shortcuts

You can use shortcuts with MDX formula editor screen:

Cmd Return (⌘⏎) save or update measure

Cmd Shift m (⌘⇧m) maximize/minimize the window of the formula editor

Cmd / (⌘/) comment or uncomment selected line/lines

MDX functions

This tutorial covered most frequently used MDX functions and examples of 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 calculation formulas then please contact eazyBI support.