Tracking test performance

[chadohman]

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)

Department	Test Subject	Test Case	Timestamp	User	Status
1	44262	100110	17/03/2020	Bill	Passed
2	46161	100111	17/03/2020	Paige	Failed
1	45886	100109	17/03/2020	Rob	Passed
1	45886	100110	17/03/2020	Rob	Failed
2	46161	100111	18/03/2020	Bob	Ready to Retest
1	45886	100110	18/03/2020	Bob	Ready to Retest
1	45886	100110	20/03/2020	Paige	Passed
2	46161	100111	20/03/2020	Bill	Passed

[v-juanli-msft · Accepted solution]

Hi @chadohman 

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.

[chadohman]

This is exactly what I'm looking for. Thank you very much for your help, @v-juanli-msft. You just made my life a whole lot easier!

[amitchandak]

@chadohman , Create a rank on Date under test subject. Then Rank =1 and Status =pass you are looking for

Create a column Rank, refer first article

For Rank Refer these links

https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...

Please Watch/Like/Share My webinar on Time Intelligence: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...
My Your Tube Tips at: https://www.youtube.com/playlist?list=PLPaNVDMhUXGYrm5rm6ME6rjzKGSvT9Jmy

Appreciate your Kudos.