Counting consecutive fails in test results

[Akmp]

I have a table containing test results.

The test results contain Buildnr, TestCase name and TestCase results.

I want to create a new measure or a new column which calculates how many times a testcase has failed in a row.

e.g if i specify Build and TestCase it should show that the Test Case has failed x times in a row.

Build 125

TC_1 -> 3 fails in a row

TC_2 -> 2 fails in a row

TC_3 -> first failure

TC_4 -> No fails

One solutions would be to create a new table where which contains a matrix where the columns are TestCase names and The Rows Build_nr.  But i do not know how to do that.

What is the prefered way to find consecutive failures?

[Akmp · Accepted solution]

Solved thanks to User Aldert.

https://stackoverflow.com/questions/57603253/counting-consecutive-fails-in-test-results-in-power-bi/...

[v-eachen-msft]

Hi @Akmp ,

You can create a new calculated column to mark the “Fail” and “Pass”.

Column 2 =
VAR a =
    CALCULATE (
        COUNTROWS ( FILTER ( test, test[Result] = "fail" ) ),
        ALLEXCEPT ( test, test[TestCase], test[Build] )
    )
RETURN
    IF ( a = BLANK (), 0, a )

Then you can refer to this case which is similar to yours.
https://community.powerbi.com/t5/Desktop/DAX-how-to-count-consecutive-identical-values/td-p/701936

Best Regards,

Eads

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