Distinct Count across all departments

[user900]

For distinct count of employees, I'm using the following measure in a card:

calculate(DISTINCTCOUNT('Table'[Emp ID])

In addition to this, I need to show a breakdown by department, but when I use the same measure with department on the x-axis, the count is higher. I think it's because one employee could appear in multiple departments due to transferring sometime throughout the year.  

Is there a way to get distinct count of employee ID across all departments?

Please and thanks

[FreemanZ]

hi @user900 

do you mean count an employee at the last department only?

[user900]

yes

[FreemanZ]

hi @user900 

try like:

Count =

VAR _table1 =

ADDCOLUMNS(

    SUMMARIZE(

         TableName,

         TableName[EmpID],

         TableName[Department]

    ),

    "Date",

    CALCULATE(MAX(TableName[Date]))

)

VAT _table2 =

TREATAS(

     _table1,

    TableName[EmpID],

    TableName[Department],

    TableName[Date]

)

RETURN

COUNTROWS(_table2)

in case of issue, please consider paste some sample data and @me

[user900]

I applied as you suggested.

I'm not familiar with VAT so I don't know how to troubleshoot this error: The syntax for 'VAT_table2' is incorrect.   Can you help?

[FreemanZ · Accepted solution]

hi @user900 

sorry it is a typo, it is VAR

[user900]

One more question.  Of that same population broken down by department, can I add a filter?  I also need to show whether the employee passed or failed a course.

[FreemanZ]

hi @user900 

Filtering on other dimension shall still work. Just try and let us know. 

[user900]

Works perfectly.  Thank you so much!