Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi
I have this table where I want it to give me unique values of MemberName who has been on duty. Unfortunately, some members do make mistakes when loggin in or out. Example below John who log in at 07:50 on the 2nd of nov by mistake and then logs out 6 minutes later. He later logs in correctly the same day but at 20:00. I want it to give me logged in at 20:00 and logged out at 08:07.
At the moment I use this formula to find the value where members logged in and logged out. But with mistakes like the ones above this doesnt work:
= Table.Group(#"Added Custom", {"ResourceID", "InvoicingDate", "ShiftName", "CrewMemberId", "CrewMemberName"}, {{"FørsteLogin", each List.Min([CrewMemberSignOnTime]), type datetime}, {"SidsteLogout", each List.Max([CrewMemberSignOffTime]), type datetime}})
It returns:
And I want it to be like this: where mistakes, which is to be determinded by a login and logout with in 30 min are taken into account in the formula
Thanks!,
Mikkel
@Anonymous Please create a measure in your table to check if duration is greater then 30 minutes
Measure =
VAR _login = MAX('Table'[TimeOfLogin])
VAR _logout = MAX('Table'[TimeOfLogout])
VAR _duration = DATEDIFF(_login,_logout,MINUTE)
RETURN IF(_duration>30,TRUE(),FALSE())
Now go to modeling tab and create a table
Final table = FILTER('Table',[Measure]=TRUE())
Hi @Anonymous
What I really were looking for was a formula in M-language or another query.
Can you make this in power query instead?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |