Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: MDX - multiple filters on different dimension with OR condition

  1. #1
    Join Date
    Nov 2008

    Default MDX - multiple filters on different dimension with OR condition

    Hello Pentaho Experts,

    First of all, I am not very sure if questions pertaining to MDX should be posted here; but if someone can help me with this.

    I have two dimensions "Reason Not Completed" and "Contract Price Type". I need to select on the measure with OR condition on the two filters i.e.

    select from Awards where `Reason Not Completed` = 'UNIQUE SOURCE' OR `Contract Price Type` = 'TIME AND MATERIALS'.

    Analyzer gives me a NonEmptyCrossJoin for the two filters resulting in only two rows of result which is AND condition and I am unable to tweak the MDX to convert this to OR.
    The MDX from the analyzer to weak is as below:

    Set [*NATIVE_CJ_SET] as 'NonEmptyCrossJoin([*BASE_MEMBERS_Award/IDV.Modifications],NonEmptyCrossJoin([*BASE_MEMBERS_Competition],[*BASE_MEMBERS_Contract]))'
    Set [*SORTED_ROW_AXIS] as 'Order([*CJ_ROW_AXIS],[Award/IDV.Modifications].CurrentMember.OrderKey,BASC,[Competition].CurrentMember.OrderKey,BASC,Ancestor([Competition].CurrentMember,[Competition].[Statutory Exception To Fair Opportunity]).OrderKey,BASC,[Contract].CurrentMember.OrderKey,BASC,Ancestor([Contract].CurrentMember,[Contract].[Contract Action Type]).OrderKey,BASC)'
    Set [*BASE_MEMBERS_Award/IDV.Modifications] as '[Award/IDV.Modifications].[Modification Number].Members'
    Set [*BASE_MEMBERS_Contract] as 'Filter([Contract].[Contract Price Type].Members,[Contract].CurrentMember.Caption Matches ("(?i).*\QTIME AND MATERIALS\E.*"))'
    Set [*BASE_MEMBERS_Measures] as '{[Measures].[Count],[Measures].[*FORMATTED_MEASURE_0]}'
    Set [*CJ_ROW_AXIS] as 'Generate([*NATIVE_CJ_SET], {([Award/IDV.Modifications].currentMember,[Competition].currentMember,[Contract].currentMember)})'
    Set [*BASE_MEMBERS_Competition] as 'Filter([Competition].[Reason Not Competed].Members,[Competition].CurrentMember.Caption Matches ("(?i).*\QUNIQUE\E.*"))'
    Set [*CJ_COL_AXIS] as '[*NATIVE_CJ_SET]'
    Member [Measures].[*FORMATTED_MEASURE_0] as '[Measures].[Obligated Amount]', FORMAT_STRING = '#,###,###,###,###.00', SOLVE_ORDER=400
    [*BASE_MEMBERS_Measures] on columns,
    Non Empty [*SORTED_ROW_AXIS] on rows
    From [Awards]

    Help from anyone will be greatly appreciated.

    Thanks in advance,

  2. #2
    Join Date
    Nov 2008

    Default Venn diagram theory

    I read in one of the SSAS threads that Set {A OR B} = Set{A} + Set{B} - Set{A AND B} according to Venn diagram theory which I recall from my school days too.

    Can someone now help me write the MDX query on these lines of if they know of any other solution?

    Thanks & Regards,

  3. #3
    Join Date
    May 2012


    did you try deleting Nonempty?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2017 Pentaho Corporation. All Rights Reserved.