title: Optimizing Dax & model for "where date between" type queries
body: <p>I am building a model to allow reporting on two seperate datasets, for this example we'l say a Students dataset &amp; a Staff dataset.</p>
  
  <p>The datasets are pretty seperate and the only real link between the two is Date, so from a model perspective, there is a Students star schema &amp; a Staff Star Schema.</p>
  
  <p>The data displayed is snapshot type data, answering questions like:
  - For a selected date, show all active employees
  - for a selected date, show all enrolled students</p>
  
  <p>This means that when a single date is selected, the model then finds all employees where the selected date falls within the employment start &amp; end date , and finds all students where the selected date falls within the enrolled start &amp; end date.</p>
  
  <p>This means i had to make a decision, how to return the correct data from each schema with a single date dimension. Creating a relationship would not work as relationships in Tabular dont allow "between" type queries, so i instead have one unrelated Date Dimension and the Dax for each model finds applicable rows.</p>
  
  <p>The problem is that its not the most performant. for perhaps 50k rows, adding a measure can take 5-10 seconds.</p>
  
  <p>Im asking if there is a better way to either write the queries, or alter the model to still let me do "between" style queries but give better performance. </p>
  
  <p>Below is an example of a dax query to return all students that were enrolled on a particular date.</p>
  
  <p>Thanks for any advice.</p>
  
  <pre><code>All Enrolled Students:=IF (
  HASONEVALUE ( 'Date'[Date] ),
  CALCULATE (
      DISTINCTCOUNT ( 'Students'[StudentID] ),
      FILTER (
          'Students',
          'Students'[StudentStartDateID] &lt;= MIN ( 'Date'[DateID] )
              &amp;&amp; 'Students'[StudentEndDateID] &gt;= MAX ( 'Date'[DateID] )
      )
  ),
  BLANK ())
  </code></pre>