mdx. Filter values from fact table before aggregate

Question

I have the simple cube with 2 dimensions: Dim1, Dim2, and one measure - value (aggregator = sum) I need to get cross table via mdx:

select non empty [Dim2].members on 0, non empty [Measures].value on 1 
from [cube]

=>

      el1 el2 el3 el4

value 12  14  45  64

this values is aggregate by dim1. ok.

Next i create calculate measure:

with [Measures].value_filter as iif([Measures].value> 15, [Measures].value, null)
select non empty [Dim2].members on 0,
non empty [Measures].value_filter on 1
from [cube]

=> we get the correct result:

     el3 el4 
value 45 64

But, how can i filter cells by non aggregate values of [Measures].value, ie real values from database?

FrankPl FrankPl · Accepted Answer · 2014-07-01T15:56:10

You can only filter by members that are contained in a dimension. If you want to filter on something, it has to be available in the cube. hence you may potentially have to add some columns as attributes to one of your dimensions.

You would do filtering as follows, assuming you want to use Member1 of hierarchy/attribute Hier2 of dimension Dim2 as the filter:

select non empty [Dim2].members on 0,
       [Measures].value on 1 
  from [cube]
 where ([Dim2].[Hier2].[Member1])

mdx. Filter values from fact table before aggregate

1 Answers