MDX - multiple filters on different dimension with OR condition

[lmcginty]

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:

With
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
Select
[*BASE_MEMBERS_Measures] on columns,
Non Empty [*SORTED_ROW_AXIS] on rows
From [Awards]

Help from anyone will be greatly appreciated.

Thanks in advance,
Pankaj

[lmcginty]

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.

http://social.msdn.microsoft.com/For...b-421c615c4406

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

Thanks & Regards,
Pankaj

[jonvet]

did you try deleting Nonempty?