Define a calculated member in MDX by filtering a measure's value

Question

I need to define a calculated member in MDX (this is SAS OLAP, but I'd appreciate answers from people who work with different OLAP implementations anyway).

The new measure's value should be calculated from an existing measure by applying an additional filter condition. I suppose it will be clearer with an example:

Existing measure: "Total traffic"
Existing dimension: "Direction" ("In" or "Out")
I need to create a calculated member "Incoming traffic", which equals "Total traffic" with an additional filter (Direction = "In")

The problem is that I don't know MDX and I'm on a very tight schedule (so sorry for a newbie question). The best I could come up with is:

([Measures].[Total traffic], [Direction].[(All)].[In])

Which almost works, except for cells with specific direction:

example

So it looks like the "intrinsic" filter on Direction is overridden with my own filter). I need an intersection of the "intrinsic" filter and my own. My gut feeling was that it has to do with Intersecting [Direction].[(All)].[In] with the intrinsic coords of the cell being evaluated, but it's hard to know what I need without first reading up on the subject :)

[update] I ended up with

IIF([Direction].currentMember = [Direction].[(All)].[Out],
    0,
    ([Measures].[Total traffic], [Direction].[(All)].[In])
)

..but at least in SAS OLAP this causes extra queries to be performed (to calculate the value for [in]) to the underlying data set, so I didn't use it in the end.

Magnus Smith Magnus Smith · Accepted Answer · 2008-10-14T12:16:59

To begin with, you can define a new calculated measure in your MDX, and tell it to use the value of another measure, but with a filter applied:

WITH MEMBER [Measures].[Incoming Traffic] AS
'([Measures].[Total traffic], [Direction].[(All)].[In])'

Whenever you show the new measure on a report, it will behave as if it has a filter of 'Direction > In' on it, regardless of whether the Direction dimension is used at all.

But in your case, you WANT the Direction dimension to take precendence when used....so things get a little messy. You will have to detect if this dimension is in use, and act accordingly:

WITH MEMBER [Measures].[Incoming Traffic] AS
'IIF([Direction].currentMember = [Direction].[(All)].[Out],
    ([Measures].[Total traffic]),
    ([Measures].[Total traffic], [Directon].[(All)].[In])
)'

To see if the Dimension is in use, we check if the current cell is using OUT. If so we can return Total Traffic as it is. If not, we can tell it to use IN in our tuple.

Define a calculated member in MDX by filtering a measure's value

2 Answers