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
ItemState = 'MI' and ItemState = 'LI'
– dani herrera