Calcuating how many times you cleaned the pool clean and grass cut for the same customer

[mhesli]

I want to see how many accounts get both pool clean and grass cut on the same day. I need a calculation to use. I have tried

CALC Pool Clean and Grass Cut Same day= CALCULATE(
DISTINCTCOUNT(Customer[Account]),
Customer[Pool Clean] = 1,
Customer[Grass Cut] = 1
)

And it does not work. Any help would be greatly appreciated!

Date	Account	Service	Pool Clean	Grass Cut
5/1/2025	5143	Pool Clean	1
5/1/2025	5143	Grass Cut		1
5/1/2025	5143	Water lawn
5/1/2025	5144	Water lawn
5/1/2025	5144	Walk dog
5/1/2025	5144	Fertalize
5/1/2025	5145	Pool Clean	1
5/1/2025	5145	Grass Cut		1
5/1/2025	5145	Water lawn
5/1/2025	5145	Walk dog
5/1/2025	5145	Fertalize
5/1/2025	5146	Water lawn
5/1/2025	5146	Walk dog
5/1/2025	5146	Fertalize
5/8/2025	5147	Pool Clean	1
5/8/2025	5147	Grass Cut		1
5/8/2025	5148	Water lawn
5/8/2025	5148	Walk dog
5/8/2025	5148	Fertalize
5/8/2025	5149	Pool Clean	1
5/8/2025	5149	Grass Cut		1
5/8/2025	5149	Water lawn
5/8/2025	5150	Walk dog
5/8/2025	5150	Fertalize
5/8/2025	5151	Pool Clean	1
5/8/2025	5151	Grass Cut		1
5/8/2025	5151	Walk dog
5/8/2025	5151	Fertalize
5/13/2025	5152	Pool Clean	1
5/13/2025	5152	Grass Cut		1
5/13/2025	5152	Water lawn
5/13/2025	5153	Walk dog
5/13/2025	5153	Fertalize
5/13/2025	5154	Pool Clean	1
5/13/2025	5154	Grass Cut		1
5/13/2025	5154	Fertalize

[v-shamiliv]

Hi @mhesli 
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[v-shamiliv]

Hi @mhesli 
I wanted to check if you had the opportunity to review the information provided by @maruthisp  and @OwenAuger . Please feel free to contact us if you have any further questions. If their response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-shamiliv]

Hi @mhesli 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[maruthisp]

Hi mhesli,

As per my understanding - Count unique combinations of  Account and Date where both services happened.
Below is the DAX expression:

PoolCleanAndGrassCutSameDay =
COUNTROWS(
FILTER(
SUMMARIZE(
CustomerServices,
CustomerServices[Date],
CustomerServices[Account],
"HasPoolClean", CALCULATE(COUNTROWS(CustomerServices), CustomerServices[Service] = "Pool Clean"),
"HasGrassCut", CALCULATE(COUNTROWS(CustomerServices), CustomerServices[Service] = "Grass Cut")
),
[HasPoolClean] > 0 && [HasGrassCut] > 0
)
)

Please let me know if this helps or need any more modifications. 
Thanks in advance.
Best Regards,

Maruthi
LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/
X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

[OwenAuger · Accepted solution]

Hi @mhesli 

Here are a couple of options. I would probably prefer the first one:

CALC Pool Clean and Grass Cut Same day = 
VAR AccountDate =
    FILTER (
        SUMMARIZE ( Customer, Customer[Account], Customer[Date] ),
        VAR Pool = CALCULATE ( SUM ( Customer[Pool Clean] ) )
        VAR Grass = CALCULATE ( SUM ( Customer[Grass Cut] ) )
        RETURN
            AND ( Grass, Pool )
    )
VAR Result =
    COUNTROWS ( GROUPBY ( AccountDate, Customer[Account] ) )
RETURN
    Result

CALC Pool Clean and Grass Cut Same day = 
VAR AccountDatePool =
    CALCULATETABLE (
        SUMMARIZE ( Customer, Customer[Date], Customer[Account] ),
        KEEPFILTERS ( Customer[Pool Clean] = 1 )
    )
VAR AccountDateGrass =
    CALCULATETABLE (
        SUMMARIZE ( Customer, Customer[Date], Customer[Account] ),
        KEEPFILTERS ( Customer[Grass Cut] = 1 )
    )
VAR Result =
    CALCULATE (
        DISTINCTCOUNT ( Customer[Account] ),
        AccountDatePool,
        AccountDateGrass
    )
RETURN
    Result