MDX - Multiple Where and/or Filter Conditions

Question

I apologize beforehand for a seemingly basic MDX question. I am trying to filter an MDX resultset based on multiple combinations of dimension attributes.

This is my dimension/measures layout:

Dimensions:

[AccidentDate]
Year
Quarter
Month
Day
Date

[ItemInformation] 
ItemState

[CoverageInformation] 
CoverageHiearchy
----UserLine
--- Coverage Code

Measures:

CTDPaid

Now, I wish to select the total amount from the [CTDPaid] measure, grouped by the [ItemInformation].ItemState attribute. However, I would like to filter the resultset of this query based on multiple filter conditions.

These conditions would be the following, and would be evaluated separately:

1. [CoverageInformation].[CoverageHiearchy].&[98]&[002] and [ItemInformation].ItemState.&[MI]
2. [CoverageInformation].[CoverageHiearchy].&[98]&[004] and [ItemInformation].ItemState.&[MI]
3. [CoverageInformation].[CoverageHiearchy].&[98]&[004] and [ItemInformation].ItemState.&[IL]
4. [CoverageInformation].[CoverageHiearchy].&[98]&[002] and [ItemInformation].ItemState.&[IL]

Essentially, if I were to port this over to a T-SQL where condition, it would constitute the following:

where

  (ItemState = 'MI' and CoverageCode = '002' and UserLine = '98')

and

  (ItemState = 'MI' and CoverageCode = '004' and UserLine = '98')

and

  (ItemState = 'IL' and CoverageCode = '002' and UserLine = '98')

and

  (ItemState = 'IL' and CoverageCode = '004' and UserLine = '98')

Putting this into an MDX slicer would not function as I believe cross joins across the same hierarchy are not supported.

Using the filter() MDX function is not working for me either.

I would greatly appreciate assistance with formulating the correct MDX query to correctly filter my resultset as outlined above.

Thank you kindly for your time

Can a CTDPaid be in 'MI' and 'IL' state at same time? If not your query have not results because your condition is ItemState = 'MI' and ItemState = 'LI' — dani herrera

Noe Zavala Noe Zavala · Accepted Answer · 2012-01-20T17:33:17

Try this solution:

SELECT 
     {...} On Columns,
     {...} On Rows
FROM  (Select 
{([CoverageInformation].[CoverageHiearchy].&[98]&[002], [ItemInformation].ItemState.&[MI]),
 ([CoverageInformation].[CoverageHiearchy].&[98]&[004], [ItemInformation].ItemState.&[MI])
 ([CoverageInformation].[CoverageHiearchy].&[98]&[004], [ItemInformation].ItemState.&[IL])
 ([CoverageInformation].[CoverageHiearchy].&[98]&[002], [ItemInformation].ItemState.&[IL])} On Columns
From [CubeName])

MDX - Multiple Where and/or Filter Conditions

1 Answers