none
MDX Expression to filter multiple dimensions

    Question

  • Hi, I am new to MDX, has built some calculated measures which are pretty much straight forward.

    Currently, I  am working on a new calculated measure using existing measures and dimensions. 

    Here are the requirements, Program UV is an existing measure, Referrer Domain, Tracking Code, Content Type are dimensions.

    Program UVs that fit ANY of the following criteria:                                                                 

    - Referrer Domain Description equals "v9"                                                                       

    - Tracking Code Type equals "v6"            
    - Tracking Code Source equals "v1"                                                                                                 

    -Tracking Code Source equals "v2"                                                                                         

    - Tracking Code Source equals "v3"                                                                                               

    - Tracking Code Source equals "v4"    
    - Tracking Code Source equals "v5"                                                                                     

    - prop4/evar4 (Content Type) equals "v7"   

    And EXCLUDE any UVs that fit the following criteria :                                                                                       

    - Tracking Code Type equals "v8"     

    SQL pseudo code:


    SELECT COUNT(Program UV) FROM A
    WHERE 
    (
    trackingcodesource = 'v1'
    OR trackingcodesource = 'v2'
    OR trackingcodesource = 'v3'
    OR trackingcodesource = 'v4'
    OR trackingcodesource = 'v5'
    OR TrackingCodeType ='v6'
    OR [Content Type] ='v7'
    OR ReferrerDomainDescription = 'v9'
    )
    AND TrackingCodeType NOT IN ('v8')


    Tried using cross join between dimensions, it works only if I use two hierarchies, any thing more than two it fails. 

    Please help.

    Wednesday, March 6, 2019 11:44 AM

Answers

  • Hi kkarnam,

    When you use {}, you need to make sure your member in it have the same hierarchy. Like your above query,hierarchies in each tuple() are the same.

    So you might need to modify your query like below(put all member in the same (), then use {})

    SUM
    (
    {([Product].[Category].&[1],[Date].[Calendar Year].&[2013],[Sales Territory].[Sales Territory Group].[All Sales Territories]),
    ([Product].[Category].[All Products],[Date].[Calendar Year].&[2014],[Sales Territory].[Sales Territory Group].[All Sales Territories]),
    ([Product].[Category].[All Products],[Date].[Calendar Year].&[all],[Sales Territory].[Sales Territory Group].&[North America])}
    ,[Measures].[Internet Sales Count])
    Best Regards,
    Zoe Zhi

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by kkarnam Tuesday, March 19, 2019 6:34 AM
    Friday, March 15, 2019 6:12 AM

All replies

  • Hi,

    Try like this :

    WHERE (
    {[DimTrackingCodeSource].[TrackingCodeSource].&[v1],[DimTrackingCodeSource].[TrackingCodeSource].&[v2],[DimTrackingCodeSource].[TrackingCodeSource].&[v3],[DimTrackingCodeSource].[TrackingCodeSource].&[v4],[DimTrackingCodeSource].[TrackingCodeSource].&[v5]},
    [DimTrackingCodeSource].[TrackingCodeType].&[v6],
    [DimContentType].[ContentType].&[v7],
    [DimReferrerDomainDescription].[ReferrerDomainDescription].&[v9]
    
    )
    You don't need to filter TrackingCodeType != v8 because you are already filtering on TrackingCodeType=v6


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    Wednesday, March 6, 2019 3:38 PM
  • Thanks for your response.

    What you have suggested does AND between the dimensions.

    But I need OR and AND operation between Dimensions 

    If you see the below pseudo code, I need records that satisfies any of the below conditions 

    trackingcodesource = 'v1'
    OR trackingcodesource = 'v2'
    OR trackingcodesource = 'v3'
    OR trackingcodesource = 'v4'
    OR trackingcodesource = 'v5'
    OR TrackingCodeType ='v6'
    OR [Content Type] ='v7'
    OR ReferrerDomainDescription = 'v9'

    and it should not have the below 

    AND TrackingCodeType NOT IN ('v8')

    For Example: If any record satisfies only ReferrerDomainDescription = 'v9' among OR's, it should not have TrackingCodeType ='v8' 

    Does that make sense?

    Thursday, March 7, 2019 9:55 AM
  • Hi kkarnam,

    You said you the query will fail when use more than two hierarchies, did you get error information?

    You could try below query to see whether it works or not(not in category[1])

       SELECT NON EMPTY { [Measures].[Internet Sales Count] } ON COLUMNS, 
       NON EMPTY { ([Sales Territory].[Sales Territory Group].[Sales Territory Group].ALLMEMBERS * [Date].[Calendar Date].[Calendar Year].ALLMEMBERS * [Product].[Category].[Category].ALLMEMBERS ) }  ON ROWS 
    FROM [Analysis Services Tutorial] 
    where({([Product].[Color].&[Black]) ,([Product].[Color].&[Red])}, 
    except([Product].[Product Categories].[Category],[Product].[Product Categories].[Category].&[1]))
    Best Regards,
    Zoe Zhi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, March 8, 2019 9:00 AM
  • @Zoe Zhi, I was trying to cross join the dimensions as below to arrive at OR condition between them

    'WITH member a
     as 
    (
    sum(
    { ([Tracking Code].[Tracking Code Type].[ALL], [ContentType].[ContentType].&[v7]),([Tracking Code].[Tracking Code Type].&[v6], [ContentType].[ContentType].[ALL])}
    ,[Measures].[b]
    )
    )
    SELECT 
    a on columns,
    [Date].[Year].&[2019] on rows

    FROM [CUBE]'

    The above code works fine since there are only two dimension hierarchies involved.

    But the below code which uses three dimensions throws error "Members, tuples or sets must use the same hierarchies in the  function."

    "WITH member [a]
     as 
    (
    sum(
    { ([Tracking Code].[Tracking Code Type].[ALL], [ContentType].[ContentType].&[v7]),([Tracking Code].[Tracking Code Type].&[v6], [ContentType].[ContentType].[ALL]),([Referrer Domain].[Referrer Domain Description].&[v9], [Guided Physician Search].[GPS Type].[ALL]),([Referrer Domain].[Referrer Domain Description].[ALL],[ContentType].[ContentType].&[v7]) }
    ,[Measures].[b]
    )
    )
    SELECT 
    [a] on columns,
    [Date].[Year].&[2019] on rows

    FROM [CUBE]

    "

    Please let me know what needs to be done
    • Edited by kkarnam Thursday, March 14, 2019 7:00 AM
    Thursday, March 14, 2019 6:59 AM
  • Hi kkarnam,

    When you use {}, you need to make sure your member in it have the same hierarchy. Like your above query,hierarchies in each tuple() are the same.

    So you might need to modify your query like below(put all member in the same (), then use {})

    SUM
    (
    {([Product].[Category].&[1],[Date].[Calendar Year].&[2013],[Sales Territory].[Sales Territory Group].[All Sales Territories]),
    ([Product].[Category].[All Products],[Date].[Calendar Year].&[2014],[Sales Territory].[Sales Territory Group].[All Sales Territories]),
    ([Product].[Category].[All Products],[Date].[Calendar Year].&[all],[Sales Territory].[Sales Territory Group].&[North America])}
    ,[Measures].[Internet Sales Count])
    Best Regards,
    Zoe Zhi

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by kkarnam Tuesday, March 19, 2019 6:34 AM
    Friday, March 15, 2019 6:12 AM
  • Thanks @Zoe Zhi.

    I have also arrived at similar query. Sum is doing an Union All, where as aggregate is doing union and removing duplicates. So, I used aggregate function.

    I also have additional requirement to exclude one value in Product category as per above example.

    So I did the below.

    AGGREGATE
    (
    {([Product].[Category].&[1],[Date].[Calendar Year].[all],[Sales Territory].[Sales Territory Group].[All Sales Territories]),
    (Except([Product].[Category].[All].Children,[Product].[Category].[v8]),[Date].[Calendar Year].&[2014],[Sales Territory].[Sales Territory Group].[All Sales Territories]),
    (Except([Product].[Category].[All].Children,[Product].[Category].[v8]),[Date].[Calendar Year].[all],[Sales Territory].[Sales Territory Group].&[North America])}
    ,[Measures].[Internet Sales Count])

    Instead of All categories with other dimensions, filtered the unwanted value that should not be present in any case.


    The results are satisfactory.

    Does this make sense?  Is there any better way of doing it?



    • Edited by kkarnam Friday, March 15, 2019 4:09 PM
    Friday, March 15, 2019 3:38 PM
  • Hi kkarnam,

    So this query help you get the correct value? If you want to use multiple attributes members in the same set, I think you need to follow above query. Or if you want to use other BI tools to analyse it, you could use slicer or filter to achieve this instead of writing MDX query.

    Thanks for your understanding and support.
    Best Regards,
    Zoe Zhi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, March 18, 2019 7:35 AM