Troubleshooting MDX calculations

This page focuses on troubleshooting a calculated measure or calculated member and determining how it gets to the result. If the report is not working as expected, please start with the Troubleshooting report data.

On this page:

When to troubleshoot MDX calculation

You should troubleshoot the calculation when the calculated measure returns

no results,
unexpected result,
an error message.

How to troubleshoot

Save a copy of your report for troubleshooting. When troubleshooting is done, you can delete this report.
Switch report to Table view. Remove other calculations that are out of the scope of troubleshooting.
Split the full MDX expression from the measure under inspection to create many report-specific calculated measures. Explore the results in each step to see which step fails. Each report-specific measure can be calculated as part of the formula or show imported measures and properties in the report context. We recommend choosing one of two debugging methods to troubleshoot the MDX calculations.
1. Top-down: from a full MDX expression, remove functions one by one until you see the predefined measures and properties.
2. Bottom-up: split the full MDX expression into the smallest parts, each for one function or predefined measure. Then, put them back together again, one by one.

Useful functions for troubleshooting

Use the SetToStr() function to see which members are included in the set.
Use the AllProperties function to test available properties and their names.
Use eazyBI autocomplete in the formula editor to avoid syntax mistakes and get correct dimensions, measures, and property names.

Data types and expressions

All values (imported and calculated) in eazyBI have one of the following data types, see the table below.

Depending on the data type, you can use functions designed for that specific data type to display existing values or calculate new ones. For example, you can perform mathematical operations (multiplication, addition) with the numeric data type. With date values, you can compare them or calculate the duration of a period.

When creating or debugging calculated measures, pay attention to the selected Formatting; it should match the data type of the expected result. By default, eazyBI attempts to determine the correct output format for calculated measures; however, in some cases, it fails to identify the correct format or produce the desired result. For example, when you test the duration, it should be Numeric; if you expect to get a date, formatting should be Date/Time .

Data type	Examples	MDX example
Numeric	100 0.045	`[Measures].[Work item count]` `[Measures].[Hours spent]`
String	'some text'	`[User].CurrentMember.Get('Email')`
Date	'06 Aug 2025' '06 Aug 2025 17:23:54'	`[Time].CurrentDateMember.StartDate` `[User].CurrentMember.GetDate('Birthday')`
Set	{Janis, Ilze, Raimonds} {2024,2025}	`{[User].[Janis],[User].[Ilze],[User].[Raimonds]}` `{[Time].[2024],[Time].[2025]}`
Logical (boolean)	true or false	`IIf(DateCompare([Issue].CurrentMember.Get('Due date'), "Today") < 0, "On Schedule", "Overdue" )`
Member	There is no member without dimensions =)	`[Time].[2024]` `[User].[Janis]`

Example using top-down method

MDX expression for "bad measure" that needs troubleshooting. The purpose of the calculation is to determine the average number of days since the last update for high-priority issues.
You can follow the steps in the Demo account: https://eazybi.com/accounts/1000/cubes/Issues/reports/4406972-troubleshooting-mdx-calculations

INCORRECT

Avg( 
  Filter(
    [Measures].[Issues due],
    [Priority].[High] > 0
  ),
  DateDiffDays(
    [Issue].CurrentHierarchyMember.Get('Updated date'), 
    Now()
  )
)

The Average function in the measure requires a set expression and a numeric expression. In the top-down method, start by splitting the code into two parts: the set and numeric expressions.
Create a new report-specific measure to test the set expression from the Average function. Use the function SetToStr to test the set result of the Filter function and its content.
```
SetToStr( 
  Filter(
    [Measures].[Issues due],
    [Priority].[High] > 0
  )
)
```
Filter function requires two parameters: a set expression and a logical expression. In the next step, create the new report-specific measure to test the set expression from the Filter function.
```
SetToStr([Measures].[Issues due])
```
It appears that the set is a constant text "Issue due", but not individual issues. Select measure "Issue due" on your columns to see what it shows - count of issues due; it is a numeric value, not a set.
A different function is required to retrieve the set of issues for calculation, such as Descendants.
```
SetToStr(
  Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue])
)
```
The original MDX expression expects to receive only issues due; therefore, a filter criterion for issues due must be added. Now use the measure "Issues due" as a filter criterion.
```
SetToStr(
  Filter(
    Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),   
    [Measures].[Issues due] > 0
  )
)
```
Looking back at the expression in step 2, filter issues by priority "High". To complete the filter criteria, combine the measure "Issues due" and the priority High in a tuple expression.
```
SetToStr(
  Filter(
    Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),   
    (
      [Measures].[Issues due],
      [Priority].[High]
    ) > 0 
  )
)
```
In the next step, test the numeric expression from the original Average function. DateDiffDays can be used to calculate the number of days between two dates.
```
DateDiffDays(
  [Issue].CurrentHierarchyMember.Get('Updated date'), 
  Now()
)
```
The code from the numeric expression in the previous step suggests that the calculation is done at the issue level. For testing purposes, you should add issue-level members from the Issue dimension to the report rows to view the "Updated date" value for each issue and validate the calculated results.
It appears that the result is empty because one of the arguments has no value. Break the DateDiffDays function even further and test what results you see at the issue level for the date property "Updated date".
```
[Issue].CurrentHierarchyMember.Get('Updated date')
```
Use autocomplete to find the correct property names and fix the measure from the previous step to the correct code. The expected outcome is date, select the Date formatting to see it as a calendar date.
```
[Issue].CurrentHierarchyMember.Get('Updated at')
```

Test if the duration since the update is calculated correctly:

DateDiffDays(
  [Issue].CurrentHierarchyMember.Get('Updated at'), 
  Now()
)

Let's put all the parts together and test the results.
CORRECT