MDX Replace Range With Filter

Question

While looking at the following answer I wanted to replace the range with a filter.

http://stackoverflow.com/questions/1361039/mdx-concurrent-calculations-based-on-a-record-time-range/1391009#1391009

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!

Answer 1

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]