Return Employee Count > 1 MDX SQL Server 2012

Question

I am trying to write a simple MDX query to get employee counts that are more than one. I am able to use filter in the rows but how can I get the query to only return values whose count is more than one?

select NON EMPTY {[Measures].[Employee Count]} ON COLUMNS,
    [Employee].[Employee ID].[Employee ID] ON ROWS
from [Human Capital]

score 2 · Accepted Answer · answered Apr 08 '18 at 02:02

Try this:

WITH MEMBER [Measures].[Employee Count 2+] as
 IIf(
  [Measures].[Employee Count]>1,
  [Measures].[Employee Count],
  Null
 )
select {[Measures].[Employee Count 2+]} ON COLUMNS,
   NON EMPTY [Employee].[Employee ID].[Employee ID].Members ON ROWS
from [Human Capital]

Return Employee Count > 1 MDX SQL Server 2012

1 Answers1