How to add condition (measure is greater than 0) in an MDX query

Question

I have the following query in MDX

With

  member [Week Count] as 
     ( 
       ([WORK ].[Complying Flag].&[COMPLYING], [Measures].[No of Work ])
/([WORK ].[Complying Flag].[(All)].[All], [Measures].[No of Work ])) *100
select  
  {[Week Count]} on columns,
  {[CLOSED DATE].[Week End Date].members} on rows
FROM [test ]

I need to add a condition where clause where [Measures].[ACT LAB HRS]>0 but it returns always error , how to correct it ?

Answer 1

You could use Filter but we would need a set to filter so something like this:

WITH
  MEMBER [Week Count] as 
     ( 
       ([WORK ].[Complying Flag].&[COMPLYING], [Measures].[No of Work ])
/([WORK ].[Complying Flag].[(All)].[All], [Measures].[No of Work ])
     ) *100
SELECT  
  {[Week Count]} on columns,
  {[CLOSED DATE].[Week End Date].members} on rows
FROM [test]
WHERE
   (
    FILTER 
      (
        [SomeDimension].[SomeHierarchy].members,
        [Measures].[ACT LAB HRS]>0
      )
   );

Another approach would be to include a HAVING clause:

WITH
  MEMBER [Measures].[Week Count] as 
     ( 
       ([WORK ].[Complying Flag].&[COMPLYING], [Measures].[No of Work ])
/([WORK ].[Complying Flag].[(All)].[All], [Measures].[No of Work ])
     ) *100
SELECT  
  {
   [Measures].[Week Count],
   [Measures].[ACT LAB HRS]
  } ON 0,
  {[CLOSED DATE].[Week End Date].members} 
  HAVING [Measures].[ACT LAB HRS]>0
  ON 1
FROM [test];