Login:Action Completed Ratio Help

[Anonymous]

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! 

[Greg_Deckler · Accepted solution]

@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]
        )
    )

[Greg_Deckler]

@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]
        )
    )       

[Anonymous]

@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!! 

[Greg_Deckler]

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
    )

[Anonymous]

Hrm @Greg_Deckler - changing to this formula for some reason made the tasks completed formula start agregating all the activities completing instead of counting the completions between each time frame on a separate line item

[Anonymous]

@Greg_Deckler I think because now the logout time is the same time as the next login time - can you help me adjust that logout time to be a bit earlier?

[Greg_Deckler]

@Anonymous Can I get an expanded data set that shows the problem? Currently I do not have an example of what is being discussed.

[Anonymous]

@Greg_Deckler https://share.getcloudapp.com/L1ugR7jW

So the issue I'm having with the new formula (as shown in the picture) is that, when there is no actual logout time, it is making the 'LogoutTime' the same exact time as the next login time. So if you look at the first row of 'createdAt' (login time), it is exactly the same as the 2nd row of 'LogoutTime' -- which is causing the numbers in 'Tasks Completed' to aggregate. How can I stop the 'LogoutTime' DateTime in your formula right before the next 'CreatedAt' (or, next login time) instead of making them equal? Thank you SO much. You are truly a lifesaver! I am beyond grateful for your help. 

[Greg_Deckler]

@Anonymous  Oh, sorry, I thought is what you wanted, that if there was no logout time then the current login should end at the next login. My bad. So, what should be happening is that if there is a Logout, then it should return that logout time. If there is no logout but there is a login, then it should return that next login time. Otherwise blank. 

So, what should it be doing instead?

[Anonymous]

If there is a logout time, return logout time.

If there is no logout time, return the next login-time -1 hour. (So if the next login time is 3/12/2020 11:00AM, it should return 3/12/2020 10:0AM) 

Else, blank.

Does that make sense @Greg_Deckler ? 

[Greg_Deckler]

Sure @Anonymous , how about this:

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 - 1/24
    )

[Anonymous]

thank you. such a mystery - that definitely solves that problem but the tasks completed are still aggregating so now I'm unsure of what the issue is @Greg_Deckler 

[Greg_Deckler · Accepted solution]

@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]
        )
    )

[Anonymous]

@Greg_Deckler yes exactly that column is still giving me aggregate of all actions completed instead of just aggregate of rows within that time frame - not sure why. https://share.getcloudapp.com/L1ugR7jW

[Greg_Deckler]

@Anonymous That would be because I thought that was what you wanted so I coded it that way! Oops again! But, the problem that I see is that at least in the sample data, if something is not completed it does not have a DateTimeCompleted value. Thus, at least in the sample data I have no way of knowing whether rows with blank DateTimeCompleted values fall into the designated time range or not. Soooo....

[Anonymous]

Well if I keep the logout time that you created, there is a start and an end date, no? So can't you help me change the tasks completed formula to just count the actions completed between createdDate and logout time? @Greg_Deckler 

[Anonymous]

Or @Greg_Deckler are you refrring to start and end date of the activity? can't we assume that was just a singular instance and it is both the start and end time? 

[Greg_Deckler]

Right, I am referring to the activities table. How are we supposed to know what login/logout range the blanks fall into?

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

[Anonymous]

@Greg_Deckler I added another filter in your original formula to filter out anything that wasn't completed by writing && 'Table2'[ActionStatus]="Completed" -- that seemed to filter out the ones I didn't need, but still did not fix the main problem I'm trying to resolvea

TasksCompleted = 
    VAR __User = [UserID]
RETURN
    COUNTROWS(
        FILTER(
            ALL('Table2'),
            'Table2'[UserID] = __User &&
                'Table2'[DateTimeCompleted] >= [DateTime] &&
                    'Table2'[DateTimeCompleted] <= [LogoutTime]
        )
    )

[Anonymous · Accepted solution]

I was able to figure it out, I believe, by using EARLIER ! @Greg_Deckler