Counting No. of clients with a sum above X

[TBSST]

Hi All, 

I am trying to work on a DAX function to allow me to count all type of jobs per client, then show me a calcucated number in a card for how many clients have met that particular threshold of a specific job. 

Client	Job
1	a
1	a
1	a
1	b
1	b
1	c
2	a
2	a
2	b
2	c
3	a
3	b
3	c
3	c

With this example, i would count all 'a'jobs, 'b' jobs and 'c' jobs, and then i will need a count of clients who have had 3 or more 'a' jobs. So with the data above, once i put the measure in a card, the count would be 2 clients. 

I do not have access to change the model or use power query. I can only do this with DAX funtions. 

thank you for your help 🙂

[Alef_Ricardo_]

Hi TBSST,

You can use the following DAX measure to count the number of clients who have had 3 or more ‘a’ jobs:

[Alef_Ricardo_ · Accepted solution]

Count of Clients with 3 or more 'a' jobs =
CALCULATE(
DISTINCTCOUNT('Table'[Client]),
FILTER(
SUMMARIZE(
'Table',
'Table'[Client],
"Count of 'a' Jobs",
CALCULATE(COUNT('Table'[Job]), 'Table'[Job] = "a")
),
[Count of 'a' Jobs] >= 3
)
)

[TBSST]

Legend. Thank you so much 🙂

[Alef_Ricardo_]

This measure first uses the SUMMARIZE function to group the data by Client and calculate the count of ‘a’ jobs for each client. Then, it uses the FILTER function to filter the summarized table to only include clients who have had 3 or more ‘a’ jobs. Finally, it uses the DISTINCTCOUNT function to count the number of unique clients in the filtered table.

You can then use this measure in a card visual to display the count of clients who have had 3 or more ‘a’ jobs.