none
MDX Count on Filtered Set of Rows

    Question

  • 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?


    Tuesday, November 26, 2013 1:02 PM

Answers

  • Hello,

    We don't need to use Case statement to achieve this requirement in this case, please refer to the following MDX query to get your expected result:

    WITH MEMBER [Measures].[BelowZERO] AS
    
    FILTER ( [Accounts].[Number].ALLMEMBERS, [Measures].[Sales Amount]<0).COUNT
    
    SELECT {[Measures].[Sales Amount],[Measures].[BelowZERO]} ON 0
    	,
    	 NON EMPTY
    	 {
    		 NonEmpty([Accounts].[Number].ALLMEMBERS,[Measures].[Sales Amount])
    	 } ON 1
    
     FROM 
    ( 
         SELECT (STRTOMEMBER('[Date].[20130801]', CONSTRAINED):STRTOMEMBER('[Date].[20130831]', CONSTRAINED)) ON COLUMNS 
         FROM [TEST_DW]
     ) 

    Regards,

    Elvis Long
    TechNet Community Support

    Friday, November 29, 2013 7:17 AM
    Moderator

All replies

  • Hello,

    We don't need to use Case statement to achieve this requirement in this case, please refer to the following MDX query to get your expected result:

    WITH MEMBER [Measures].[BelowZERO] AS
    
    FILTER ( [Accounts].[Number].ALLMEMBERS, [Measures].[Sales Amount]<0).COUNT
    
    SELECT {[Measures].[Sales Amount],[Measures].[BelowZERO]} ON 0
    	,
    	 NON EMPTY
    	 {
    		 NonEmpty([Accounts].[Number].ALLMEMBERS,[Measures].[Sales Amount])
    	 } ON 1
    
     FROM 
    ( 
         SELECT (STRTOMEMBER('[Date].[20130801]', CONSTRAINED):STRTOMEMBER('[Date].[20130831]', CONSTRAINED)) ON COLUMNS 
         FROM [TEST_DW]
     ) 

    Regards,

    Elvis Long
    TechNet Community Support

    Friday, November 29, 2013 7:17 AM
    Moderator
  • Try the below one...This should just return only the count, not the account members

    WITH
      MEMBER [Measures].[BelowZero] AS
        CASE
          WHEN
            [Measures].[MyValue] < 0
          THEN 1
          ELSE 0
        END
      MEMBER [Measures].[BelowZeroCount] AS
        Sum
        (
          [Accounts].[Number].[Number].ALLMEMBERS
         ,[Measures].[BelowZero]
        )
    SELECT
      {[Measures].[BelowZeroCount]} ON COLUMNS
    FROM
    (
      SELECT
          StrToMember('[Date].[20130801]',CONSTRAINED): StrToMember('[Date].[20130831]',CONSTRAINED) ON COLUMNS
      FROM
     [TEST_DW]
    );

    Saturday, November 30, 2013 12:54 PM