Get the count of Failures between Success for each Serial Number

[Anonymous]

Hi,

I have a dataset which has all the failures and success for each serial Number. I want the the count of failure which happend just before the recent success.

Input::

SerialNumber    Status    DateTime          CountryCode

AAAA               Failure     10-10-2018       US

AAAA               Failure     10-11-2018       US

AAAA               Success   10-12-2018      US

AAAA               Failure     09-10-2018      US

AAAA               Success    09-11-2018      US

Output::

Serial Number RecentFailure Country Code

AAAA               2                   US

SampleDatSet.JPG

[Zubair_Muhammad · Accepted solution]

@Anonymous 

Give this a shot please

Measure =
COUNTROWS (
    CALCULATETABLE (
        Table1,
        FILTER (
            Table1,
            [Status] = "Failure"
                && MAXX (
                    TOPN (
                        1,
                        FILTER (
                            Table1,
                            [SerialNumber] = EARLIER ( [SerialNumber] )
                                && [DateTime] > EARLIER ( Table1[DateTime] )
                        ),
                        [DateTime], ASC
                    ),
                    [Status]
                ) = "Success"
        )
    )
)

[Anonymous]

Thanks @Zubair_Muhammad . It Worked. 

Just a follow up to this question . As you have created this as a measure, I need to further breakdown to do some processing and convert this structure into below structure could you please guide me on this.

Retry-0 is All Devices with zero failure

Retry-1 is no. of Devices in the country with 1 numbers. of attempt of failure before suceess 

Retry-2  is no. of Devices in the country with 2 numbers. of attempt of failure before suceess 

Retry -3 is no. of Devices in the country with 3 numbers. of attempt of failure before suceess

Country Code Retry -0 Retry-1 Retry -2 Retry-3

US                     2              2             1         0

GB                     0              1            0          3

Thanks for your help!

[Zubair_Muhammad]

@Anonymous 

can you share some sample data with expected results... i will try to help

[Anonymous]

@Zubair_Muhammad , 

I cannot share the exact data as business policy but I have created below Input which we got from above solution u suggested::

Input::

CountryCode      SerialNumber     FailureCount

US                        AAA                     4

US                        EEE                       5

US                        BBB                      3

US                        CCC                      1

DE                        ABC                       2

DE                        CDE                       0

Above table 1 st row explains we have serial No. AAA which has 4 retries before it could success in US country.

Output what we need::

CountryCode      Retry-0              Retry-1           Retry-2          Retry-3         Retry-X(All greater than 3)

US                         0                         1                   0                    1                      2

DE                        1                          0                  1                     0                    0

Now what we want is we should have country wise how many serial Number retried in respective retry columns

.For example, using the first row of our input table in US country we have 2(AAA,EEE) serial Number which falls under retry-X category.

Hope I have explained correctly. Please guide me through. Thanks in advance