Trying to Find Unique Values For Same date

Hi all,

I am stuck and in need of some help.

I am trying to calculate the First time right passes and unique completed items for users based on calculated columns. Let me explain the current state and the requirement.

I have the data in below format.

Mandy will complete cases for customers. John will check them. Each time a checking is complete, a unique sequence ID will be given. Sometimes Mandy gets everything correct on the first go. This is called a First Time Pass. Sometimes she will get things wrong and John will fail the case back to Mandy and she will correct the errors and John will check again and pass the case. In this case, it is not a First Time Pass.

In the above table, 111, 113 and 114 are First Time Passed cases. 116 has been worked twice without being failed. This is due to a system issue. In this case we will check the gap between both records and if the gap is more than 2 months, we will mark them both as First Time Passes. However, 115 is passed twice on the same day. Since its the same day, we will only count it as 1 First Time Pass.

The first requirement is to calculate the number of First Time Passes. To do this I have introduced a calculated column.

The second requirement is to calculate the number of completed unique cases. For example, there are 7 unqiue cases completed in the above data set.

Below is the desired outcome.

Below is the code for FirstTimePass column:

FirstTimePass = 
IF(
    NOT(ISBLANK(qc[Checked Date])),
    VAR CustomerID = qc[Customer ID]
    VAR Date = qc[Checked Date]
    VAR User = qc[User]
    VAR FirstQCResult =
        CALCULATE(
            MIN(qc[Result]),
            ALLEXCEPT(qc, qc[Customer ID]),
            qc[User] = User,
            qc[Checked Date] <= Date
        )
    VAR HasPassedBefore =
        CALCULATE(
            COUNTROWS(qc),
            ALLEXCEPT(qc, qc[Customer ID]),
            qc[User] = User,
            qc[Checked Date] < Date,
            qc[Checked Date] >= Date - 60,
            qc[Result] = "Passed"
        ) > 0
    RETURN IF(FirstQCResult = "Passed" && NOT(HasPassedBefore), "Yes", BLANK()),
    BLANK()
)

Below is the code for "Unique" column:

Unique = 
    VAR CustomerID = qc[Customer ID]
    VAR Date= qc[Checked Date]
    VAR Analyst = qc[User]
    VAR FirstQCResult =
        CALCULATE(
            MIN(qc[Result]),
            ALLEXCEPT(qc, qc[Customer ID]),
            qc[User] = Analyst,
            qc[Checked Date] <= Date
        )
    VAR HasPassedBefore =
        CALCULATE(
            COUNTROWS(qc),
            ALLEXCEPT(qc, qc[Customer ID]),
            qc[User] = Analyst,
            qc[Checked Date] < Date,
            qc[Checked Date] >= Date- 210,
            qc[Result] = "Passed"
        ) > 0
    VAR HasFailedBefore =
        CALCULATE(
            COUNTROWS(qc),
            ALLEXCEPT(qc, qc[Customer ID]),
            qc[User] = Analyst,
            qc[Checked Date] < Date,
            qc[Checked Date] >= Date- 210,
            qc[Result] <> "Passed"
        ) > 0
    RETURN IF(
        NOT(ISBLANK(qc[Checked Date])),
        IF(
            (FirstQCResult = "Passed" || FirstQCResult <> "Passed") &&
            NOT(HasFailedBefore || HasPassedBefore),
            "Yes",
            BLANK()
        ),
        BLANK()
    )

This is the outcome:

The issues are as below.

Above in RED : 112 is failed on the same day but passed again later on the same day. The unique column should only be "Yes" for sequence 556. But both rows for 112 is marked as "Yes".

Above in Green: Because both records were Passed on the same day for 115 due to system issue, both FirstTimePass and Unique should be blank for sequence 570 - or the second entry for 115.

Appreciate if someone can point me in the right direction. I have tried using EARLIER and RANKX and M query but to no avail. Many thanks in advance.