Using DAX, how do I calculate a table when filtering on a related table?

• jonharding2204

  SSChasing Mays

  Points: 610

  More actions

  April 12, 2016 at 8:32 am

  #148872

  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.

  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.

  I can calculate the distinct count of Machine IDs using this formula:

  2Month Distinct Machines:=CALCULATE (

  [Distinct Machine Ids],

  FILTER(

  ALL( 'Log Date' ),

  ( 'Log Date'[YearMonthNumber] >= MAX( 'Log Date'[YearMonthNumber] ) - 3 )

  && ( 'Log Date'[YearMonthNumber] <= MAX( 'Log Date'[YearMonthNumber] ) - 1 )

  )

  )

  Where 'Distinct Machine Ids' is calculated as:

  :=DISTINCTCOUNT([MachineId])

  and where 'YearMonthNumber' is calculated on the 'Log Date' table as:

  =('Log Date'[YearKey] - MIN('Log Date'[YearKey])) * 12 + 'Log Date'[MonthOfYearKey]

  (effectively this gives the number of the month in the context of the entire date dimension).

  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?

  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:

  CALCULATETABLE (

  ADDCOLUMNS (

  SUMMARIZE ( FactCheckForUpdates, FactCheckForUpdates[MachineId] ),

  "meh", CALCULATE ( SUM ( FactCheckForUpdates[CFUPing] ) )

  ),

  FactCheckForUpdates[LogDateKey] > DATE ( 2016, 4, 1 )

  )

  but I'm not sure how to use the 'Log Date' table here.

  Any help massively appreciated!