CODEWITHSUNDEEP
ssasmdxolap
user1365247
user1365247

Reputation: 337

MDX Count on Filtered Set of Rows

I need to write an MDX query which goes through a filtered set of rows from my TEST_DW, and for each one it will return BelowZero=1 if MyValue<0, else BelowZero=0. I managed to complete this using the MDX below:

WITH 
MEMBER [Measures].[BelowZero] AS 
    case when ([Measures].[MyValue]) < 0
    then 1
    else 0
end 
SELECT NON EMPTY 
{ 
    [Measures].[BelowZero]
} ON COLUMNS, 
NON EMPTY 
{ 
    ([Accounts].[Number].ALLMEMBERS ) 
} 
ON ROWS FROM 
( 
    SELECT (STRTOMEMBER('[Date].[20130801]', CONSTRAINED) : STRTOMEMBER('[Date].[20130831]', CONSTRAINED)) ON COLUMNS 
    FROM [TEST_DW]
)

The problem is that I now need to sum up all the values for BelowZero. In other words, this MDX should just give me one result back, telling me how many Accounts have Measure.MyValue<0. I am not interested in knowing WHICH accounts are <0, but just a count.

Any ideas please?

Upvotes: 0

Views: 5547

Answers (1)

FrankPl
FrankPl

Reputation: 13315

WITH 
MEMBER [Measures].[BelowZero] AS 
    case when ([Measures].[MyValue]) < 0
        then 1
        else 0
    end
MEMBER [Measures].[BelowZeroCount] AS
    SUM([Accounts].[Number].ALLMEMBERS, [Measures].[BelowZero])
SELECT NON EMPTY 
{ 
    [Measures].[BelowZeroCount]
} ON COLUMNS
 FROM 
( 
    SELECT (STRTOMEMBER('[Date].[20130801]', CONSTRAINED) : STRTOMEMBER('[Date].[20130831]', CONSTRAINED)) ON COLUMNS 
    FROM [TEST_DW]
)

or

WITH 
MEMBER [Measures].[BelowZeroCount] AS
    Filter([Accounts].[Number].ALLMEMBERS, [Measures].[MyValue]) < 0).Count
SELECT NON EMPTY 
{ 
    [Measures].[BelowZeroCount]
} ON COLUMNS
 FROM 
( 
    SELECT (STRTOMEMBER('[Date].[20130801]', CONSTRAINED) : STRTOMEMBER('[Date].[20130831]', CONSTRAINED)) ON COLUMNS 
    FROM [TEST_DW]
)

Upvotes: 2

Related Questions