Merging client data within a set time period

[LarousseG]

I have data from a machine we use for diagnosing issues with clients.

Each time we use the machine to do a sweep, we get a record of that sweep with the machine ID, ClientID, user ID, date, time, and sweep intensity.

I need to know how many diagnoses we have conducted over a time period (e.g. a month).

The problem is, sometimes we use the machine more than once for a single diagnosis with a client (i.e. the first sweep didn't work or doesn't give us enough information, and needs to be repeated, sometimes 3-4 times).

I need a way to count those multiple sweeps as a single diagnosis for our records.

But a client may have a single sweep done in the morning, then another one done in the afternoon- and those are both separate diagnoses. So I can't just merge all sweeps with the same ClientID.

Plus, the machines are running 24/7. So I might do a sweep for Client A at 11:50pm, then a second one at 12:02am. Both are for the same diagnosis. But on different dates.

When we have multiple sweeps done for the same diagnosis, they normally happen within a few minutes of each other. They would never take place over more than 2 hours. And when the same client has multiple diagnoses done within a 24 hour period, they would never happen within 2 hours of each other.

Here is an example report I can pull:

Machine ID	ClientID	User ID	Sweep Date	Sweep Time	Sweep Intensity
M1	33	JohnT	5/04/2022	23:40	1
M1	33	JohnT	5/05/2022	12:05	3
M1	33	JohnT	5/05/2022	12:17	3
M1	75	JohnT	5/05/2022	12:28	2
M4	24	AnnaW	5/05/2022	9:50	3
M4	65	PeteJ	5/05/2022	14:20	3
M3	33	GeorgeH	5/05/2022	15:30	3

Client 33 had 3 sweeps as part of the same diagnosis in the evening of the 4th/morning of the 5th. They also had a separate sweep that afternoon, on a different machine, as a different diagnosis.

I need the query/dax(?) to look at this, and tell me- there were 5 diagnoses (from 7 sweeps).

This is the first time I've used Power Query and Power BI, and I've got no idea where to start. Any tips in getting on to the right path would be appreciated.

[v-jialluo-msft · Accepted solution]

^{Hi @LarousseG ,}

^{Please follow these steps:}

^{(1) Create a new measure}

COUNT = 
SUMX (
    'Table',
    DIVIDE (
        1,
        CALCULATE (
            COUNTROWS (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[User ID] = MAX ( 'Table'[User ID] )
                        && 'Table'[Sweep Date] = MAX ( 'Table'[Sweep Date] )
                        && ABS ( DATEDIFF ( 'Table'[Sweep Time], MAX ( 'Table'[Sweep Time] ), SECOND ) ) / 3600 < 2
                )
            )
        )
    )
)

^{(2)Final output}

^{Best Regards,}

^{Gallen Luo}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}