title: Using DAX, how do I calculate a table when filtering on a related table?
body: <p>I'm trying to find the intersect of two result sets using DAX, but I'm really struggling to get the two result sets calculated.</p>
  
  <p>I have a fact table, FactCheckForUpdates, that has a relationship to a date table called 'Log Date'.  FactCheckForUpdates contains Machine IDs, and I want to return the IDs for the last 2 complete months.</p>
  
  <p>I can calculate the distinct count of Machine IDs using this formula:</p>
  
  <pre><code>2Month Distinct Machines:=CALCULATE (
      [Distinct Machine Ids], 
      FILTER( 
          ALL( 'Log Date' ), 
          ( 'Log Date'[YearMonthNumber] &gt;= MAX( 'Log Date'[YearMonthNumber] ) - 3 ) 
          &amp;&amp; ( 'Log Date'[YearMonthNumber] &lt;= MAX( 'Log Date'[YearMonthNumber] ) - 1 )
      )
  )
  </code></pre>
  
  <p>Where 'Distinct Machine Ids' is calculated as:</p>
  
  <pre><code>:=DISTINCTCOUNT([MachineId])
  </code></pre>
  
  <p>and where 'YearMonthNumber' is calculated on the 'Log Date' table as:</p>
  
  <pre><code>=('Log Date'[YearKey] - MIN('Log Date'[YearKey])) * 12 + 'Log Date'[MonthOfYearKey]
  </code></pre>
  
  <p>(effectively this gives the number of the month in the context of the entire date dimension).</p>
  
  <p><strong>Can anyone help me update the [2Month Distinct Machines] expression so that instead of returning the distinct count of Machine IDs in the period, it returns a table of the machine IDs?</strong></p>
  
  <p>I've tried using the CALCULATETABLE function, but it won't accept the MAX aggregate on the date filter.  The closest I've gotten is this formula:</p>
  
  <pre><code>CALCULATETABLE (
      ADDCOLUMNS (
          SUMMARIZE ( FactCheckForUpdates, FactCheckForUpdates[MachineId] ),
          "meh", CALCULATE ( SUM ( FactCheckForUpdates[CFUPing] ) )
      ),
      FactCheckForUpdates[LogDateKey] &gt; DATE ( 2016, 4, 1 )
  )
  </code></pre>
  
  <p>but I'm not sure how to use the 'Log Date' table here.</p>
  
  <p>Any help massively appreciated!</p>