topic DISTINCTCOUNT with 2 different tables? in DAX Commands and Tips

DISTINCTCOUNT with 2 different tables?

Anonymous — Mon, 26 Sep 2022 12:49:24 GMT

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

I have 2 tables:

AD table:

Employee table:

(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?

Greg_Deckler — Mon, 26 Sep 2022 13:03:28 GMT

Anonymous Try:

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

DimaMD — Mon, 26 Sep 2022 13:08:10 GMT

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]) )

Anonymous — Mon, 26 Sep 2022 13:09:24 GMT

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 — Mon, 26 Sep 2022 13:25:20 GMT

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

Greg_Deckler — Mon, 26 Sep 2022 13:29:58 GMT

Anonymous Try the attached PBIX below sig.