Forum Discussion

LarousseG's avatar
LarousseG
Regular Visitor
3 years ago
Solved

Merging client data within a set time period

 

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.

  • 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.

1 Reply

  • v-jialluo-msft's avatar
    v-jialluo-msft
    Community Support

    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.