title: DISTINCT COUNT basket analysis between multiple measures with shared dimension
body: <p>Let's say I have a cube with two different distinct count measures, call them <code>Measure1</code> and <code>Measure2</code>. Both of these measures contain a common dimension, <code>Dimension1</code>, which is counted by both measures.</p>
  
  <p><strong>What I need to do is return a distinct count of <code>Dimension1</code> members that exist in both <code>Measure1</code> and <code>Measure2</code></strong>, after appropriate filtering on each measure as required.</p>
  
  <p>I can define MDX queries for both <code>Measure1</code> and <code>Measure2</code> individually and get distinct counts, but I need to be able to "overlap" the result to avoid double-counting the members that exist in both sets.</p>
  
  <p>Note: in the actual scenario, there are more than 2 measures involved, and all MDX queries will be dynamically constructed (the user defines which measures and dimension criteria are included).</p>
  
  <p>Can this be done in SSAS/MDX? If not, is there another Microsoft tool/feature that can? The minimum requirement for the system is SQL Server 2008 R2 Standard Edition.</p>
  
  <p>Honestly I have no idea where to start. Google turned up nothing like this (I saw some basket analysis stuff involving a single measure, but I'm unsure if or how to apply that to my scenario). I'm not an SSAS/MDX/BI expert by any means.</p>
  
  <p>There are two alternatives that I can think of:</p>
  
  <ol>
  <li>Use <code>DRILLTHROUGH</code> using the individual MDX queries and (essentially) <code>COUNT DISTINCT</code> the results.</li>
  <li>Use T-SQL on the data warehouse source database. (May be difficult to account for all scenarios efficiently.)</li>
  </ol>
  
  <p>We do have a requirement to also be able to drillthrough, so I'll probably have to implement solution #1 anyway, but it would be nice to have a more efficient way to obtain just the counts, as counts will be needed <em>far</em> more frequently.</p>