distinct count employees in transaction table that are not in roster table

[jjl007]

I've been searching for an answer and checked this community, finding subject but not answering question completely. I am trying to do what is common in Access database: count the number of employees (distinct) in transactions table that are not in related Roster table. Both tables are related on employee ID.  I've tried CALCULATE(DISTINCTCOUNT(TransactionTable[EmpID]),ISBLANK(RELATED(RosterTable[EmpID])) and DAX will not accept this.  Any suggestions are very much appreciated.  Jeff

[Greg_Deckler · Accepted solution]

@jjl007 - Try:

Measure =
  COUNTROWS(
    DISTINCT(
      EXCEPT(
        SELECTCOLUMNS('TransactionTable',"EmpID",[EmpID])
        SELECTCOLUMNS('RosterTable',"EmpID",[EmpID])
      )
    )
  )

[Greg_Deckler · Accepted solution]

@jjl007 - Try:

Measure =
  COUNTROWS(
    DISTINCT(
      EXCEPT(
        SELECTCOLUMNS('TransactionTable',"EmpID",[EmpID])
        SELECTCOLUMNS('RosterTable',"EmpID",[EmpID])
      )
    )
  )

[jjl007 · Accepted solution]

Thanks for the help. Your solution did work after I changed it slightly (minor to allow acceptance).  For information, I also got my original (Access-like query) measure to work: =CALCULATE(DISTINCTCOUNT(TransactionTable[EmpID]),ISBLANK(Roster[EmpID])) and both of these measures give the same number (result).   Jeff

[Greg_Deckler]

@jjl007 - Yep! Always more than one way to solve something in DAX!