How to find if user performed only login or login + other actions

[Anonymous]

Hi, please guide me towards the right solution

I have an audit dataset like below:

AuditID	Action	UserName	Date	LoginWithFurtherActions
AuditID1	Login	User1	Day1	TRUE
AuditID2	Update	User1	Day1	TRUE
AuditID3	Login	User1	Day2	FALSE
AuditID4	Login	User2	Day3	TRUE
AuditID5	Login	User2	Day3	TRUE
AuditID6	Update	User2	Day3	TRUE
AuditID7	Login	User2	Day4	FALSE

I need to calculate the column "LoginWithFurtherActions" to find if a user only logged-in within a day or logged-in and updated some records.

Example:

1) If User1 performed action "Login" and action "Update" within Day1 then "LoginWithFurtherActions" = TRUE;

2) If User2 performed only action "Login" without action "Update" within Day4 then "LoginWithFurtherActions" = FALSE.

Many thanks for the help:)

Oleh

[Anonymous · Accepted solution]

Hi @Anonymous ,

According to my understand, you want to set True() when the user in the same day Login and Update, set False() when only Login , right?

You could use the following formula to creata a measure:

Measure =
VAR _date =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[UserName] = MAX ( 'Table'[UserName] )
                && 'Table'[Date] = MAX ( 'Table'[Date] )
                && 'Table'[Action] = "Update"
        )
    )
RETURN
    IF ( MAX ( 'Table'[Date] ) = _date, TRUE (), FALSE () )

The final output is shown below:

Please take a look at the pbix file here.

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

[Anonymous]

Thanks! It works

[littlemojopuppy]

I would suggest NOT using a calculated column for this...measures are almost always preferable to calculated columns.

Here's code for a measure...

Users With Further Actions:=
	VAR	UsersLoggingIn = 
		CALCULATETABLE(
			VALUES(ActivityLog[UserName]),
			ActivityLog[Action] = "Login"
		)
	VAR	UsersDoingMore = 
		CALCULATETABLE(
			VALUES(ActivityLog[UserName]),
			ActivityLog[Action] <> "Login"
		)
	RETURN
	
	IF(
		ISEMPTY(UsersDoingMore),
		FALSE(),
		TRUE()
	)