CODEWITHSUNDEEP
ssasmdxcubeolap-cube
Raii
Raii

Reputation: 303

MDX Select Count of Measure where Measure > 0

I want to count the number of
distinct (dimension) USER GUID on a
particular (dimension) Month where (measure) M1 > 0 and (measure) M2 > 0

My query: 

 WITH 
  MEMBER [Measures].[M1 Count] AS 
    Count
    (
      NonEmpty
      (
          NonEmpty
          (
            [AAUser].[USER GUID].[USER GUID].MEMBERS
           ,[Measures].[M1]
          )
        * 
          {[Measures].[M1]}
      )
    ) 
  MEMBER [Measures].[T2 Count] AS 
    Count
    (
      NonEmpty
      (
        NonEmpty
        (
          [AAUser].[USER GUID].[USER GUID].MEMBERS
         ,[Measures].[T2]
        )
       ,{[Measures].[T2]}
      )
    ) 
SELECT 
  {
    [Measures].[M1 Count]
   ,[Measures].[T2 Count]
  } ON COLUMNS
 ,[AATime].[Month].[Month].ALLMEMBERS ON ROWS
FROM [MyCube]
WHERE 
  {
      StrToMember("[AATime].&[2013-11-01T00:00:00]")
    : 
      StrToMember("[AATime].&[2014-10-31T00:00:00]")
  };

The result I’m getting is:
enter image description here

It’s wrong because I’m selecting NONEMPTY, which would return everything (even if the value is 0). Can anyone please modify my query and filter (where [Measures].[M1] > 0 and [Measures].[M2] > 0)

Upvotes: 0

Views: 3465

Answers (1)

whytheq
whytheq

Reputation: 35605

(Not tested) Maybe you can use the function filter:

WITH 
  MEMBER [Measures].[M1 Count] AS 
    Count
    (
      NonEmpty
      (
          NonEmpty
          (
            filter(
              [AAUser].[USER GUID].[USER GUID].MEMBERS
              , ([Measures].[M1] > 0 AND [Measures].[M2] > 0)
            )
           ,[Measures].[M1]
          )
        * 
          {[Measures].[M1]}
      )
    ) 
  MEMBER [Measures].[T2 Count] AS 
    Count
    (
      NonEmpty
      (
        NonEmpty
        (
            filter(
              [AAUser].[USER GUID].[USER GUID].MEMBERS
              , ([Measures].[M1] > 0 AND [Measures].[M2] > 0)
            )          
         ,[Measures].[T2]
        )
       ,{[Measures].[T2]}
      )
    ) 
SELECT 
  {
    [Measures].[M1 Count]
   ,[Measures].[T2 Count]
  } ON COLUMNS
 ,[AATime].[Month].[Month].ALLMEMBERS ON ROWS
FROM [MyCube]

But maybe something along these lines will work better?

WITH 
  MEMBER [Measures].[M1 Count] AS 
    Sum
    (
      [AAUser].[USER GUID].[USER GUID]
     ,IIF
      (
        [Measures].[M1] > 0 AND [Measures].[M2] > 0
       ,1
       ,NULL
      )
    ) 
  MEMBER [Measures].[T2 Count] AS 
    Sum
    (
      NonEmpty
      (
        [AAUser].[USER GUID].[USER GUID]
       ,[Measures].[T2]
      )
     ,IIF
      (
        [Measures].[M1] > 0 AND [Measures].[M2] > 0
       ,1
       ,NULL
      )
    ) 
SELECT 
  {
    [Measures].[M1 Count]
   ,[Measures].[T2 Count]
  } ON COLUMNS
 ,[AATime].[Month].[Month].ALLMEMBERS ON ROWS
FROM [MyCube];

Upvotes: 3

Related Questions