DISTINCTCOUNT with 2 different tables?

[Anonymous]

I can seem to make work      DISTINCTCOUNT ( Employee[Dept] ).

I have 2 tables:

AD table:

AD group	Employee
ITP	John
ADMIR	Tim
ADMIR	Mary
ADMIR	Johana
TJJ	Aria
TJJ	Armeena
TJJ	Juana

Employee table:

Employee	Dept
John	Marketing
Tim	Marketing
Mary	Marketing
Johana	HR
Aria	HR
Armeena	HR
Juana	HR

(The tabled are connected through column Employee)

It is a 1 to many relationship.

I just want to count distinct dept on each AD group.

That is:

ITP      1

ADMIR     2

TJJ    1

I have used  DISTINCTCOUNT ( Employee[Dept] ) and then I drag AD group to the table and it doesnt work.

How can I make DISTINCTCOUNT work when using 2 tables?

[DimaMD · Accepted solution]

Hi @Anonymous If your tables are not connected, then you can use this measure

CALCULATE( 
    DISTINCTCOUNT ( 'Employee table'[Dept] ), 
     TREATAS( VALUES('AD table'[Employee]),'Employee table'[Employee])
)

[Greg_Deckler]

@Anonymous Try:

Measure = COUNTROWS(DISTINCT('Employee'[Dept]))

[Anonymous]

@Greg_Deckler  does not seem to work, I get value 2   for all. It seems that because the column is from another table the distinctcoutn does not work.

[Greg_Deckler]

@Anonymous Try the attached PBIX below sig. 

[Greg_Deckler]

@Anonymous Is this a measure or a calculated column? If calculated column you will need to use RELATED.