Filtering in MDX Calculated Members

BI development is not something I do that often, but from time to time you need to develop a custom report in TFS. I recently had to battle a MDX problem that someone who does more BI development I am sure why have had no issue with; but as with most of these blog posts (or my long term memory as I think of it) I thought it worth a post in case it helps anyone else

What I was trying to do was produce a table, as shown below, that would allow me to report for a project on the total estimated time in the requirements work items (an estimate made in the requirements planning phase of the project), the total original estimate in the tasks work items (the estimate made by the developers during iteration planning) and compare both with the actual completed time from the task work items.

Project

Estimated Effort (from Requirements)

Estimated Effort (from Task)

Actual Effort (from Tasks)

Proj A

10

11

12

Proj B

15

14

21

Proj C

23

20

24

Proj D

9

10

10

The problem is that in the TFS warehouse both the requirement and task work item estimate in stored in the

[Measures].[Microsoft_VSTS_Scheduling_OriginalEstimate]

measure. In the MDX query I needed to add a pair of calculated measures that would filter for the two work item type.

This is where I stumbled, should I use IIF() or FILTER(). So I tried both. However, working in Report Builder 3 neither seemed to work. I seemed to end up with either an empty column or not filtering at all and showing the sum of all the work items estimates irrespective of the filter.

The first tip is stop work in Report Builder, this is great for making the report look good, but not the best for resolving MDX issues. Use the query tool within SQL Management Studio. As soon as I did this I saw some of my efforts were returning #Err. This explained my empty columns, Report Builder seemed to just swallow the #Err and give me an empty column.

A a bit more digging I found the form that did what I needed, and ended up with the following form for the MDX in the calculated measures

MEMBER [Measures].[EstimatedWorkForTasks] AS '([Measures].[Microsoft_VSTS_Scheduling_OriginalEstimate], [Work Item].[System_WorkItemType].[Task])'

By editing the MDX in SQL Management Studio it was fair quicker to develop and debug

Once I was happy with the MDX, I could cut and pasted it back into Report Builder and fix the layout of the report. And all without using either IIF() or FILTER().