Getting leaf nodes in an MDX query.

Question

I'm trying to create an MDX query on the TFS cube which gets the historical values of the Estimate field. For each task I want to get the last estimate for each person who set the Estimate field. For example if Bob the project manager sets the estimate to 24 hours and then Dave the developer sets it to 32 hours, I want to create a query which shows the variance of each of their estimates to the real hours worked [Work Completed].

The problem I'm running into is that the historical values of Work Items in TFS are tracked with revs (revisions), but TFS puts both a positive and negative value in a single rev for work items which are changed. Which makes the sum look like 0 / empty cell.

For example Bob sets the estimate to 24 in rev 2. Then Dave sets it to 32 in rev 3. TFS adds another row to the data warehouse of -24 for rev 2. When this gets rolled up I can't see bob's estimate.

How Do I select only the positive values? I tried using the filter function and this returns the right members, but the value of the cell is still null

with
member [Last Estimate] as [Measures].[Microsoft_VSTS_CMMI_Estimate]
SELECT  
    {nonempty([Changed By].[Person].[All].Children*[Last Estimate])} ON 0,
    {nonempty([Work Item].[System_Id].[System_Id]*Filter([Work Item].[System_Rev].[All].Children, [Measures].[Microsoft_VSTS_CMMI_Estimate] > 0))} ON 1

FROM [Work Item History]
WHERE [Team Project].[Team Project].&[29];

Here is the results

Work     rev     Bob             Dave
Item         Last Estimate  Last Estimate
7446      2  (null)          (null)
7446      3  (null)          32.0

Every row should have a value for one of the two people.

Thanks in advance!

Answer 1

The filter function filters the members of the dimension but this is not enough because a member can contain for example both 24 and -24. Try filtering the measure itself with the Iif function:

with 
member [Last Estimate] as 
'
Iif([Measures].[Microsoft_VSTS_CMMI_Estimate] > 0, [Measures].[Microsoft_VSTS_CMMI_Estimate], 0)
'
SELECT   
    {nonempty([Changed By].[Person].[All].Children*[Last Estimate])} ON 0, 
    {nonempty([Work Item].[System_Id].[System_Id]*Filter([Work Item].[System_Rev].[All].Children, [Measures].[Microsoft_VSTS_CMMI_Estimate] > 0))} ON 1 

FROM [Work Item History] 
WHERE [Team Project].[Team Project].&[29];