topic Re: Using DISTINCTCOUNT to find distinct names in table in DAX Commands and Tips

Using DISTINCTCOUNT to find distinct names in table

sjade — Thu, 24 Mar 2022 16:52:15 GMT

I have a table with the following setup:

Team	name	skill	rating
database	john smith	SQL server	3
database	john smith	DAX	3
database	jimmy plank	SQL Server	2
engineering	susie q	ETL	2
engineering	susie q	SQL	3
engineering	issac walker	ETL	1

There are about 50 skills per person. And there are a varying number of people on each team. I am trying to write a DAX measure to count the distinct amount of employees on each team. Any suggestions?

Re: Using DISTINCTCOUNT to find distinct names in table

Jihwan_Kim — Thu, 24 Mar 2022 17:02:56 GMT

Hi,

Please check the below picture and the attached pbix file.

Employee count in each team: = DISTINCTCOUNT(Data[name])

Re: Using DISTINCTCOUNT to find distinct names in table

Whitewater100 — Thu, 24 Mar 2022 17:04:05 GMT

Hi:

If your table name = Team then

DC Team =

var teamname = SELECTEDVALUE(Team[Team])

return

CALCULATE(DISTINCTCOUNT(Team[name]), FILTER(ALL(Team),

Team[team] = teamname))

Re: Using DISTINCTCOUNT to find distinct names in table

sjade — Thu, 24 Mar 2022 18:16:52 GMT

This is what I am looking for. is there anyway to add this value to the source table as a column?

Re: Using DISTINCTCOUNT to find distinct names in table

Whitewater100 — Thu, 24 Mar 2022 18:24:45 GMT

Hi:

Yes:

Dist CT =

CALCULATE(

DISTINCTCOUNT(Team[name]),

FILTER(ALL(Team),Team[Team] = EARLIER(Team[Team])))

Re: Using DISTINCTCOUNT to find distinct names in table

Whitewater100 — Thu, 24 Mar 2022 18:26:42 GMT

Hi Jihwan:

I'm sorry if I answered after you. I did not see any answer yet when I answered. Thank you..

Re: Using DISTINCTCOUNT to find distinct names in table

sjade — Thu, 24 Mar 2022 18:28:35 GMT

Thank you so much!