Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Tracking test performance

Hello,

I've been wracking my brain around how to best solve this question but haven't came up with something acceptable.

 

I have 38000 rows of data that documents each time a test is performed and the result of that test. A Test Subject belongs to a Department. A Test Subject may have more than one Test Case, and if a Test Case fails, it will be retested until it passes. I'm looking for a way to know how many test subjects pass a test the first time vs those that needed to be retested.

 

Thank you for any insight you can provide!

 

Sample data: (simulated due to sensitivity)

DepartmentTest SubjectTest CaseTimestampUserStatus
14426210011017/03/2020BillPassed
24616110011117/03/2020PaigeFailed
14588610010917/03/2020RobPassed
14588610011017/03/2020RobFailed
24616110011118/03/2020BobReady to Retest
14588610011018/03/2020BobReady to Retest
14588610011020/03/2020PaigePassed
24616110011120/03/2020BillPassed
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    as assumed, there may be two cases:

    one:

    Measure =
VAR mindate =
    CALCULATE (
        MIN ( 'Table'[Timestamp] ),
        ALLEXCEPT (
            'Table',
            'Table'[Test Subject],
            'Table'[Test Case]
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Status] ),
        FILTER (
            'Table',
            'Table'[Timestamp] = mindate
        )
    )


Measure 2 =
VAR mincase =
    CALCULATE (
        MIN ( 'Table'[Test Case] ),
        ALLEXCEPT (
            'Table',
            'Table'[Test Subject]
        )
    )
RETURN
    CALCULATE (
        [Measure],
        FILTER (
            'Table',
            'Table'[Test Case] = mincase
        )
    )


pass a test the first time = CALCULATE(DISTINCTCOUNT('Table'[Test Subject]),FILTER('Table','Table'[Measure 2]="Passed"))

needed to be retested = CALCULATE(DISTINCTCOUNT('Table'[Test Subject]),FILTER('Table','Table'[Measure]="Failed"))

     

    another:

     

    Measure 3 =
IF (
    FIND (
        "Failed",
        CONCATENATEX (
            ALLEXCEPT (
                'Table',
                'Table'[Test Subject]
            ),
            [Measure],
            ","
        ),
        1,
        0
    ) >= 1,
    "Failed",
    "Passed"
)

pass a test the first time2 = CALCULATE(DISTINCTCOUNT('Table'[Test Subject]),FILTER('Table','Table'[Measure 3]="Passed"))

needed to be retested 2 = CALCULATE(DISTINCTCOUNT('Table'[Test Subject]),FILTER('Table','Table'[Measure 3]="Failed"))

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies