Post reply

Equivalent of T-SQL 'NOT IN' for MDX

  • 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

  • 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 🙂

  • 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

  • July 2, 2009 at 4:52 am

    #1019029

    Thanks very much to both of you. 🙂

  • 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.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply