title: Define a calculated member in MDX by filtering a measure's value
body: <p>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).</p>
  
  <p>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:</p>
  
  <ul>
  <li>Existing measure: "Total traffic"</li>
  <li>Existing dimension: "Direction" ("In" or "Out")</li>
  <li>I need to create a calculated member "Incoming traffic", which equals "Total traffic" with an additional filter (Direction = "In")</li>
  </ul>
  
  <p>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:</p>
  
  <pre><code>([Measures].[Total traffic], [Direction].[(All)].[In])
  </code></pre>
  
  <p>Which almost works, except for cells with specific direction:</p>
  
  <p><img src="https://i.stack.imgur.com/z3BxZ.png" alt="example"></p>
  
  <p>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 <code>[Direction].[(All)].[In]</code> 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 :)</p>
  
  <p><strong>[update]</strong> I ended up with </p>
  
  <pre><code>IIF([Direction].currentMember = [Direction].[(All)].[Out],
      0,
      ([Measures].[Total traffic], [Direction].[(All)].[In])
  )
  </code></pre>
  
  <p>..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.</p>