Forum Discussion
Login:Action Completed Ratio Help
I am looking to create a ratio measure of how often someone completes a task when they login to the app (as opposed to just opening the app and not completing a task) -- or, put another way, the average number of tasks completed for every login. I have the following 2 tables that have NO relationship
LOGINS:
| UserID | Action | DateTime |
| 1 | Login | 2/20/2020 10:00AM |
| 2 | Login | 2/20/2020 10:00AM |
| 3 | Login | 2/20/2020 11:00AM |
| 1 | Logout | 2/20/2020 10:45AM |
| 2 | Logout | 2/20/2020 10:30AM |
| 3 | Logout | 2/20/2020 11:30AM |
| 1 | Login | 2/23/2020 2:00PM |
| 2 | Login | 2/23/2020 3:00PM |
| 3 | Login | 2/23/2020 3:30PM |
Activities Completed
| UserID | ActionID | ActionStatus | DateTimeCompleted |
1 | 40 | Completed | 2/20/2020 10:05AM |
| 2 | 45 | Pending | |
| 3 | 44 | Completed | 2/20/2020 10:10AM |
| 1 | 34 | Pending | |
| 2 | 345 | Completed | 2/23/2020 3:05PM |
| 2 | 365 | Completed | 2/23/2020 3:06PM |
| 1 | 23 | Completed | 2/23/2020 2:05PM |
| 2 | 34 | Completed | 2/23/2020 3:08PM |
| 3 | 50 | Completed | 2/23/2020 3:35PM |
Again, it is 1 measure that sums the average # of items COMPLETED:Every login (ratio) -- these tables have no relationship -- I do have a calendar table and a user lookup table with users and IDs that they are both connected to
Greg_Deckler or amitchandak could really use a superhero help here to make my clients happy! Thank you for your help in advance!
Anonymous unsure of what you mean? I am assuming you are referring to this column?
TasksCompleted = VAR __User = [UserID] RETURN COUNTROWS( FILTER( ALL('Table2'), 'Table2'[UserID] = __User && 'Table2'[DateTimeCompleted] >= [DateTime] && 'Table2'[DateTimeCompleted] <= [LogoutTime] ) )I was able to figure it out, I believe, by using EARLIER ! Greg_Deckler
19 Replies
Anonymous Perhaps something like two columns like below. PBIX is attached.
LogoutTime = IF( [Action] = "Login", MINX(FILTER('Table',[DateTime] > EARLIER([DateTime]) && [UserID] = EARLIER([UserID]) && [Action] = "Logout"),[DateTime]), BLANK() ) TasksCompleted = VAR __User = [UserID] RETURN COUNTROWS( FILTER( ALL('Table2'), 'Table2'[UserID] = __User && 'Table2'[DateTimeCompleted] >= [DateTime] && 'Table2'[DateTimeCompleted] <= [LogoutTime] ) )Greg_Deckler this was incredibly helpful, I just learned that not all logins have a logout - so if I wanted to add to this and say also look at login time and any action completed that happened before the next login (or, if easier, any action completed within 4 hours of the login) -- how would I update your formula? Thank you, thank you, thank you!!
Awesome. OK, well. Hmm...
Perhaps something like:
LogoutTime = VAR __Logout = IF( [Action] = "Login", MINX(FILTER('Table',[DateTime] > EARLIER([DateTime]) && [UserID] = EARLIER([UserID]) && [Action] = "Logout"),[DateTime]), BLANK() ) VAR __NextLogin = IF( [Action] = "Login", MINX(FILTER('Table',[DateTime] > EARLIER([DateTime]) && [UserID] = EARLIER([UserID]) && [Action] = "Login"),[DateTime]), BLANK() ) RETURN SWITCH(TRUE(), NOT(ISBLANK(__Logout)),__Logout, __NextLogin )