Distinctcount with filters

[Anonymous]

Hi all, 

Since I'm new to PowerBI and DAX the following question.. 

I'm trying to count the number of clients that are purchasing a certain service. Example of table (services):

Clientnmbr       Service
11         A
11         B
25         A
37         A

37         B

37         C
44         A
54         A
54         C
65         C

I'm looking for a dax-formula that counts the number of clients (distinctcount) that use 1 service (answer=1), 2 services (answer=2) or 3 services (answer=1)... 

Thanks! Steven

[Anonymous]

Distinct clients = DISTINCTCOUNT('Table'[Clientnmbr])

(use if you want to filter on service in report)

Distinct clients service A = CALCULATE([Distinct clients], KEEPFILTERS('Table'[Service]="A"))

(use if you want a new mesure for a specific service)

or combine like this:

Distinct clients service A = CALCULATE(DISTINCTCOUNT('Table'[Clientnmbr]), KEEPFILTERS('Table'[Service]="A"))

[Tahreem24]

@Anonymous ,

Create a mesure like below:

DistinctCount = DISTINCTCOUNT('TableName'[Client])

Don't forget to give thumbs up 👍 and accept this as a solution if it helped you.

[Tahreem24]

@Anonymous ,

After creating DAX measure you'll get expected output as per below screen shot:

Measure = DISTINCTCOUNT('TableName'[ClientID])

Don't forget to give thumbs up 👍 and accept this as a solution if it helped you.

[Anonymous]

Thanks, but this shows the number of times that a specific service is used. I'm looking for the formula that shows the # of clients that use 1, 2 or 3 services. 

[Anonymous · Accepted solution]

You could create a calculated table showing the number of services by client:

SUMMARIZE('Table', Clientnmbr, "ServiceCount", DISTINCTCOUNT('Table'[Service])

Or use that inside of any other DAX. But of you want to be able to filter on the number you need a calculated table since you can't filter on measures.