Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
I have managed to resolve this using another DAX formula, thank you for your assistance with this query though.
I have managed to resolve this using another DAX formula, thank you for your assistance with this query though.
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" )
)
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.
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:
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:
I have tried the above however it doesn’t return the results that I require, please see further explanation to the query:
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".
@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:
and
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.
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".
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |