Equivalent of T-SQL 'NOT IN' for MDX

• Lempster

  SSCoach

  Points: 15651

  More actions

  June 30, 2009 at 9:01 am

  #207696

  Hello,

  I have the following MDX query:

  SELECT { [Year].[All Date].[2009] } *

  { [Date].[All].[2009].[Quarter 1].[February] ,

  [Date].[All].[2009].[Quarter 1].[March] } *

  { [Dim Service Types].[Service Type].[Service Type Name].[Income Protection Comparison] }

  ON 0,

  { ( [Measures].[Batch Count] ) , ([Measures].[Reporting Quote Count] ) } ON 1

  FROM [Quotes]

  WHERE ( [Dim Product].[Products].[Product Category].&[Protection],

  [Dim User Type].[User Type Category].&[IFA & Controlled Distribution] )

  It works fine and returns the expected results, but I also want to exclude certain users from the results. Those users (let's call them UserX and UserY) exist in the [Dim Users].[Users] hierarchy.

  How can I exclude those users and does the exclusion need to appear in the slicer or possibly as a named set constructed using a filter?

  Pointers in the right direction gratefully received!

  Thanks

  Lempster

• fayazabdu

  SSC Veteran

  Points: 261

  More actions

  June 30, 2009 at 10:53 am

  #1017776

  Hi

  I think we can use EXCEPT function. Some thing like this

  Except( [Dim Users].[Users].AllMembers, { [Dim Users].[Users].&[UserX], [Dim Users].[Users].&[UserY]})

  I would prefer to use this function in columns.

  All the best

• denglishbi

  SSCrazy Eights

  Points: 8831

  More actions

  June 30, 2009 at 5:59 pm

  #1018078

  Yeah, you can use Except or - (minus).

  Except (MDX)

  - (Except) (MDX)

  MDX Essentials: Basic Set Functions: The EXCEPT() Function

  ----------------------------------------------------------------------------------------
  Dan English - http://denglishbi.wordpress.com

• Lempster

  SSCoach

  Points: 15651

  More actions

  July 2, 2009 at 4:52 am

  #1019029

  Thanks very much to both of you.

• rajeevsys

  SSC Journeyman

  Points: 85

  More actions

  September 1, 2011 at 8:11 am

  #1375978

  Hi i have same problem with little different requirement, following is my query

  select NON EMPTY{

  [Measures].[Clicks],

  [Measures].[YAP Delivered Impressions],

  [Measures].[YAP Scheduled Impressions],

  [Measures].[Total Measured Revenue Constant]} on columns,

  NON Empty (FILTER(

  [Order].[Order Number].[Order Number],

  [Measures].[Clicks] > 0 and

  [Measures].[YAP Delivered Impressions] >0 and

  [Measures].[YAP Scheduled Impressions] > 0 and

  [Measures].[Total Measured Revenue Constant] >0),

  [Advertiser].[Advertiser Name].children,

  [Sales Hierarchy].[Sales Region].[Sales Region],

  [Sales Hierarchy].[Sales Rep Name].children,

  [Order Item Type].[Order Item Type].[Order Item Type],

  {Except([Order].[Campaign].[Campaign].members,

  {[Order].[Campaign].&[Unknown],[Order].[Campaign].&[adx booking]})},

  {Except([Sales Hierarchy].[Sales Rep Name].members,

  {[Sales Hierarchy].[Sales Rep Name].&[Unknown]})}

  )

  on rows from YAPDM where {(

  [Time].[Fiscal Month].&[201101],

  [Ad Offering].[Ad Offering].&[Audience Display])}

  Above query is returning expected result, i want to add more filter such as i do not want to select "unknown" from [Sales Hierarchy].[Sales Rep Name].&[unknown], if i am using the condition with another except then it is giving error like "The Sales Rep Name hierarchy is used more than once in the Crossjoin function" how can i solve it. please suggest.