topic Re: Calcuating how many times you cleaned the pool clean and grass cut for the same customer in DAX Commands and Tips

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

mhesli — Wed, 07 May 2025 23:26:47 GMT

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

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

OwenAuger — Thu, 08 May 2025 00:24:13 GMT

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

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

maruthisp — Thu, 08 May 2025 01:30:22 GMT

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

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

Anonymous — Tue, 13 May 2025 12:05:24 GMT

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.

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

Anonymous — Thu, 22 May 2025 12:45:19 GMT

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.

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

Anonymous — Mon, 26 May 2025 12:20:24 GMT

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.