MDX Replace Range With Filter

While looking at the followin开发者_如何学Gog answer I wanted to replace the range with a filter.

MDX - Concurrent calculations based on a "record time range"?

The MDX is:

with member [Measures].[TotalUsed] as sum({[Date].[YQM].&[20090501]:[Date].[YQM].&[20090907]}, [Measures].[Used])
select    {[Measures].[Total Used]}on columns,    
          {[Color].[Colors].[All].MEMBERS}on rows
from [Cube]

I'm trying to replace the Date range with a filter like this:

with member [Measures].[TotalUsed] as sum(FILTER([Date].[YQM], [Date].[YQM] < [Date].[YQM].&[20090907]), [Measures].[Used])
select    {[Measures].[Total Used]}oncolumns,
          {[Color].[Colors].[All].MEMBERS}on rows
from [Cube]

What is the conditional statement looking for in terms of comparing values? Any Help would be great!

Thanks!

The Filter statement needs a SET and then an EXPRESSION to filter on. You can drop this right inside your SUM function. The expression part of the filter can be almost anything, but it has to evaulate to true/false for each cell in the SET.

-- FILTER ( SET, EXPRESSION)

It's a bit tough not knowing what your data is structured like but your statment would probably end up like the following, filtering rows with less than 50 'UnUsed' for your timeperiods, and then summing them as an example.

`

WITH MEMBER [Measures].[TotalUsed] 
 AS SUM (FILTER ( [Date].[YQM].members, [Measures].[UnUsed] > 50 ), 
                  [Measures].[Used] )

SELECT    {[Measures].[Total Used]} ON COLUMNS,          
{[Color].[Colors].[All].MEMBERS} ON ROWS 
FROM [Cube]