How to add a condition (measure greater than 0) in an MDX request

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 is the sentence where [Measures].[ACT LAB HRS]>0 but it always returns an error, how to fix it?

Answer 1

You can use Filter, but we need a filtering kit 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 sentence HAVING:

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];