title: MDX query to count number of rows that match a certain condition (newest row for each question, client group)
body: <p>I have the following fact table:</p>
  
  <pre><code>response_history_id     client_id       question_id     answer
  1                       1               2               24
  2                       1               2               27
  3                       1               3               12
  4                       1               2               43
  5                       2               2               39
  </code></pre>
  
  <p>It holds history of client answers to some questions. The largest response_history_id for each client_id,question_id combination is the latest answer for that question and client.</p>
  
  <p>What I want to do is <strong>to count the number of clients whose <em>latest answer</em> falls within a specific range</strong></p>
  
  <p>I have some dimensions:</p>
  
  <pre><code>question                associated with question_id  
  client                  associated with client_id  
  response_history_id     associated with response_history_id  
  range                   associated with answer. 0-20 low, 20-40 = medium, &gt;40 is high  
  </code></pre>
  
  <p>and some measures:</p>
  
  <pre><code>max_history_id as max(response_history_id)
  clients_count  as disticnt count(client_id)
  </code></pre>
  
  <p>Now, I want to group only the latest answers by range:</p>
  
  <pre><code>select
  [ranges].members on 0,
  {[Measures].[clients_count]} on 1
  from (select [question].[All].[2] on 1 from [Cube])
  </code></pre>
  
  <p>What I get is:</p>
  
  <pre><code>Measures                All     low     medium  high
  clients_count           2       0       2       1
  </code></pre>
  
  <p>But what I wanted (and I can't get) is the calculation based on the latest answer:</p>
  
  <pre><code>Measures                All     low     medium  high
  clients_count           2       0       1       1
  </code></pre>
  
  <p>I understand why my query doesn't give me the desired result, it's more for demonstration purpose. But I have tried a lot of more complex MDX queries and still couldn't get any good result.</p>
  
  <p>Also, I can't generate a static view from my fact table because later on I would like to limit the search by another column in fact table which is timestamp, my queries must eventually be able to get _the number of clients whose latest answer to a question before a given timestamp falls within a specific range.</p>
  
  <p>Can anyone help me with this please? 
  I can define other dimensions and measures and I am using iccube.</p>