MDX to exclude and filter sets

• transfering

  Old Hand

  Points: 369

  More actions

  December 1, 2013 at 10:25 am

  #395921

  For several days, I've been trying to write mdx select correctly, but the result I get every time is either more than the right number or an error.

  I have to count my orders but exclude orders with Customer Flag 1 in Year 2010.

  Basically, I have a Fact table with Orders + DIM Customer + DIM Date. Than I have:

  WITH MEMBER [count] as count(exists([DIM ORDERS].[ORDERS ID].children,

  {EXCEPT([DIM CUSTOMER].[FLAG].children,FILTER([DIM CUSTOMER].[FLAG].&[1] ,[DIM CUSTOMER].[FLAG].currentmember IS [DIM_DATE].[YEAR].&[2010]))},

  'FACT_ORDERS'

  ))

  SELECT [count] on 0

  from [Cube]

  Where am I going wrong? Is this a correct MDX for my task. Please help me.

• PB_BI

  SSCoach

  Points: 17463

  More actions

  December 3, 2013 at 7:14 am

  #1670382

  I had a similar thing once where I had to so a sum of a count only for an unknown year and excluding certain statuses.

  I did this

  SUM({(FILTER([Repair Date].[Year].MEMBERS,[Repair Date].[Year].CURRENTMEMBER.NAME = 'Unknown'),

  EXCEPT([Status].[Status].[Status], {[Status].[Status].&[Closed],[Status].[Status].&[Deleted]}))}

  ,[Measures].[My Measure])

  Hope that helps 🙂

  ---

  I'm on LinkedIn