MDX Query Statemnet Change to allow for OR condition

Gary McBride 1 Reputation point
2022-03-09T22:46:49.157+00:00

Hi all, i have little to no MDX experience only really seeing it after setting up my data selections through the PowerPivot Analytical Services wizard that helps me select from our OLAP Cube. Unfortunately I cannot get the Table Import Wizard to be as flexible as I need it to be. For Example I can filter my data by Campaign Type and week number to create the customer list I need. However I can only do this for one set of selection rules but not two. For example, I want Campaign type "Other" from Wk462020, Wk172021 and it provides me the data for my Campaigns table. Easy!!. But now I also want to add to this data from Campaign Type "OtherX1" from Wk492021, Wk042022 & Wk052022 .

However the wizard can not seem to handle this OR statement "Other" from Wk462020, Wk172021 OR "OtherX1" from Wk492021, Wk042022 & Wk05202

I have copied the MDX code for each o the two selection rule groups and created two separate queries in Powerquery where I append them into a single Campaigns table but the refresh takes too long for only 38,000 records.

Ideally I'd like to change the MDX code to incorporate the OR in one go but I do not have the skills can anyone help?

I have added the MDX for the OtherX1 selection below. The MDX for the Other campaign type is of course the same except for the Campaign type & week numbers and I have added only the difference between the two sets of code below the OtherX1 code.

I hope someone is able to assist me with this immediate problem and advise me how difficult or easy it might be for me to learn basic MDX to allow me to 'edit' the Wizards MDX in future. Many thanks.

= AnalysisServices.Database("server", "Database", [**Query="

SELECT NON EMPTY { [Measures].[Qty Mailed], [Measures].[Cust Count], [Measures].[Total AUD Bkd], [Measures].[Qty Resp Bkd >$0] } ON COLUMNS, NON EMPTY { ([Customer].[Cust Number].[Cust Number].ALLMEMBERS * [Scheduled Date].[Schedule Hierarchy].[Schedule Week].ALLMEMBERS * [Scheduled Date].[Calendar 6L Hierarchy].[Calendar Date].ALLMEMBERS * [Mailing Region].[Region Name].[Region Name].ALLMEMBERS * [Package].[Pkg No].[Pkg No].ALLMEMBERS * [Package].[Pkg Title].[Pkg Title].ALLMEMBERS * [Stock Combination].[Piece Hierarchy].[Piece Name].ALLMEMBERS * [Campaign Type].[Campaign Type Hierarchy].[Campaign Type Name].ALLMEMBERS * [Order Criteria].[Recency Code].[Recency Code].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [Campaign Type].[Campaign Type Hierarchy].[Campaign Type Name].&[OX1] } ) ON COLUMNS FROM ( SELECT ( { [Scheduled Date].[Schedule Hierarchy].[Schedule Week].&[202204], [Scheduled Date].[Schedule Hierarchy].[Schedule Week].&[202205], [Scheduled Date].[Schedule Hierarchy].[Schedule Week].&[202149] } ) ON COLUMNS FROM [OrderIncome])) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

", Implementation="2.0"])**

The Other Query differs only here:
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [Campaign Type].[Campaign Type Hierarchy].[Campaign Type Name].&[OTH] } ) ON COLUMNS FROM ( SELECT ( { [Scheduled Date].[Schedule Hierarchy].[Schedule Week].&[202117], [Scheduled Date].[Schedule Hierarchy].[Schedule Week].&[202046] } )

Thank you.

Microsoft Deployment Toolkit
Microsoft Deployment Toolkit
A collection of Microsoft tools and documentation for automating desktop and server deployment. Previously known as Microsoft Solution Accelerator for Business Desktop Deployment (BDD).
835 questions
{count} votes