Calculated field in tableau to find if the first row is Success or Failure

Question

I have the below data (from a table) with fields TransactionDate, UserID, and the StatusDesc. The goal is I want to show the userID, where the UserID does not have a login failure (indicated by statusDesc Column - 'Rejected') occurring prior on the same day as a successful login ( Sucessfull Login is indicated by statusDesc Column - 'Success').

Actual DATA

            TransactionDate                 UserId                      StatusDesc

            2018-12-02 00:00:01.957     [email protected]     Rejected
            2018-12-02 00:00:14.907     [email protected]     Success
            2018-13-02 00:00:22.390     [email protected]     Success
            2018-13-02 00:00:28.610     [email protected]     Rejected
            2018-14-02 00:00:22.390     [email protected]              Success
            2018-14-02 00:00:28.610     [email protected]              Rejected

Expected Result

            TransactionDate                 UserId                      StatusDesc
            2018-13-02 00:00:22.390     [email protected]     Success
            2018-14-02 00:00:22.390     [email protected]              Success 

Answer 1

You can use an LOD (Level of Detail) expression in a Calculated Field for this. LOD's can be tricky to wrap your head around at first, but once you've used them a few times they become very handy.

1. Create a new calculated field under Dimensions called "First Rejection of Day". This will have the timestamp of the first rejection of the day for the user, or null if there have been no rejections. Use the following code:

{ FIXED [UserId], DATE([TransactionDate]) : MIN(
    IF [StatusDesc] = "Rejected" THEN
      [TransactionDate]
    END
  )
}

2. Create another calculated field called "Has Prior Rejection". Use the following code:

ISNULL([First Rejection of Day]) = FALSE AND [First Rejection of Day] <= [TransactionDate]

3. Drag Has Prior Rejection to Filters, and select "False". This will filter out all records that have a prior rejection.

Screenshot in Tableau