Re: Removing 'invalid rows' from query-result list (TFS)

[Deepak]

(This is a reply to the thread:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1042606&SiteID=1
)

Since I don't have the actual Team System cube design, here's my best
guess: [Measures].[Current Work Item Count] is in the Current measure
group, hence this [ValidRows] expression returns only the current
attributes associated with each SystemID:

NONEMPTY
(
( [Assigned To].[Person].[Person],
[Work Item].[System_Id].[System_Id],
[Work
Item].[System_WorkItemType].[System_WorkItemType],
[Work Item].[System_Title].[System_Title] ),
{[Measures].[Current Work Item Count]}
)

Instead, what you want are the latest attributes on or before
STRTOMember(@prmEndDate). So you should apply NonEmpty to the History
measure group, where the corresponding measure might be like:
[Measures].[Work Item History Count]. But the date range needs to be
considered as well, making for a more complex [ValidRows] (see Chris
Webb's blog for discussion of this filtering technique):

Extract(Filter(

NonEmpty(([Work Item].[System_Id].[System_Id],
{STRTOMember(@prmStartDate):STRTOMember(@prmEndDate)},

[Assigned To].[Person].[Person], [Work
Item].[System_WorkItemType].[System_WorkItemType], [Work
Item].[System_Title].[System_Title]),

{[Measures].[Work Item History Count]}) as [AllRows],

Not([AllRows].Current.Item(0) is [AllRows].Item(Rank([AllRows].Current,
[AllRows])).Item(0))),

[Assigned To].[Person], [Work Item].[System_Id], [Work
Item].[System_WorkItemType], [Work Item].[System_Title])

This new version of [ValidRows] would then be used in the original
version of the query:

SELECT

{ [ValidRows] } ON ROWS,

NON EMPTY
{
[Measures].[Completed Work On Period Start],
[Measures].[Completed Work On Period End],
[Measures].[Completed_Work]
} ON COLUMNS

FROM [Team System]

WHERE ([Company_Common_AssignedGroup].&[Electronics])