Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pardeepd84
Helper III
Helper III

Countif and Sumif to calculate difference

Hi,

 

I have a large dataset which contains Client ID, employee ID, planned visit, planned start time, actual start date, actual start time. I need to count the number of times 2 employees visit the same visit client on the same day and arrive within 5 minutes of each other.  

 

In the example below employee ID 25 and 26 arrived within 5 minutes of each other (employee ID 25 arrived at 14:33 on 10/02/2021 and employee ID 26 arrived at 14:35 on 10/02/2021) on the same day (10/02/2021) to see client ID 123.  This is then recorded as "ok", if they didn't I need it to record "not ok".

 

Please see sample data below, column AH is what I want the query to return. 

 

Arrive within 5 mins.JPG

1 ACCEPTED SOLUTION
pardeepd84
Helper III
Helper III

I have managed to resolve this using another DAX formula, thank you for your assistance with this query though. 

View solution in original post

8 REPLIES 8
pardeepd84
Helper III
Helper III

I have managed to resolve this using another DAX formula, thank you for your assistance with this query though. 

v-henryk-mstf
Community Support
Community Support

Hi @pardeepd84 ,

 

According to your needs, I did the following tests and got the correct expected results:

Col =
VAR cur_index = 'Table'[Index]
VAR next_time =
    CALCULATE (
        MAX ( 'Table'[Time] ),
        FILTER (
            'Table',
            'Table'[Index] = cur_index + 1
                && 'Table'[Client ID] = EARLIER ( 'Table'[Client ID] )
        )
    )
RETURN
    next_time
M = 
IF (
    MAX ( 'Table'[Col] ) = BLANK (),
    BLANK (),
    IF ( ABS ( MAX ( 'Table'[Col] ) - MAX ( 'Table'[Time] ) <= 5 ), "OK", "Not OK" )
)

v-henryk-mstf_0-1618560469241.png

Below is the sample pbix file.


If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

 

Best Regards,
Henry

 

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

Hi @v-henryk-mstf 

 

I have tried this with my own data and also changed the time in per your example however it does not produce the same results.  Please see screenshot below:

 

 

pardeepd84_0-1619108865208.png

I would have expected to see for Client 1234 in Col the result of 17 however it appears as 13, the same is for client ID 5678 - it returns the answer as 35 not 56 as expected.  

 

I have followed your example:

 

Col =
VAR cur_index = 'Table'[Index]
VAR next_time =
CALCULATE (
MAX ( 'Table'[Time] ),
FILTER (
'Table',
'Table'[Index] = cur_index + 1
&& 'Table'[Client ID] = EARLIER ( 'Table'[Client ID] )
)
)
RETURN
next_time
 
M =
IF (
MAX ( 'Table'[Col] ) = BLANK (),
BLANK (),
IF ( ABS ( MAX ( 'Table'[Col] ) - MAX ( 'Table'[Time] ) <= 5 ), "OK", "Not OK" )
)
 
This does not produce the expected results, please could you advise if I am doing something wrong here.

5 Mins sample data.JPG

I have tried the above however it doesn’t return the results that I require, please see further explanation to the query:

 

  • Where the client has 2 visits planned at the same time on the same day (i.e 08/02/2021 at 08:15) and 2 employee attend within 5 minutes of each other then return the result of “Ok”
  • Where the client has 2 visits planned at the same time on the same day (i.e 08/02/2021 at 08:15) and 2 employees but do not arrive within 5 minutes of each other then return the result of “Not Ok”

In the same data below:

 

Client ID 1234 had 2 visits planned on 08/02/2021 at 08:15 and both employees (Jenny and Jo) arrived within 5 minutes of each other – Jenny arrived at 07:43:13 and Jo arrived at 07:43:38.

 

Client ID 5678 had 2 visits planned on 08/02/2021 at 19:05 but the employees (Fred and Ned) did not arrive within 5 minutes of each other – Fred arrived at 19:01:53 and Ned arrived at 19:17:29. Ned arrived 15 minutes later than Fred, therefore this would be recorded as "Not Ok". 

 

amitchandak
Super User
Super User

@pardeepd84 , Please check if this new column can help

 

New column =

var _1= countx(filter(Table, [Client ID] =earlier([Client ID]) && [EMP ID] <> earlier([EMP ID]) && [Actual Date] <> earlier([Actual Date]) &&
[Actual Start Date] >=earlier([Actual Start Date]) -time(5,0,0) && [Actual Start Date] <= earlier([Actual Start Date]) +time(5,0,0)),[Client ID])+0

 

return

if(_1  >0, "Not OK", "OK")

Hi @amitchandak ,

 

I need a calculation as follows: 

 

  • Where the client has 2 visits planned at the same time on the same day (i.e 08/02/2021 at 08:15) and 2 employee attend within 5 minutes of each other then return the result of “Ok"

and 

 

  • Where the client has 2 visits planned at the same time on the same day (i.e 08/02/2021 at 08:15) and 2 employees but do not arrive within 5 minutes of each other then return the result of “Not Ok”.

I have tried your suggestion however it does not return the correct results.

 

In your example it states time(5,0,0) - would this not be calculating 5 hours rather than 5 minutes?  Please correct me if I am wrong. 

 

@amitchandak 

 

I have managed to get the formula to work however I don't think it is counting correctly.  

 

I need the formula to count the client ID and if they have 2 visits scheduled at the same time on the same day then return me the result if 2 employees arrived within 5 minutes of each other i.e

 

In the sample data Client ID 1234 had 2 planned visits on 10/02/2021 at 08:30, Jo and Jenny arrived within 5 minutes of each other and so it records it as "ok".  However for Client ID 6789 James and Emma did not arrive within 5 minutes of each other and so it records it as "Not Ok". 

 

pardeepd84_0-1618415109420.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.