cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
khannadivyeh Frequent Visitor
Frequent Visitor

Count "Failure" Records with recent status "Success" for each Serial Number.

Hi, 

I have a dataset which has all the failures and success for each serial Number. I want to get the count of failures records for every serial number whose last record status is "Success" with  two conditions::

1. Either count failure records iterating back till any Success record is found for each serial Number. We need to count records between past success to latest success for each serial Number.

2. Or if there is no Success record in past then count all the failures for each serial Number.

 

Below is the sample data set::

Input::

SerialNumber    Status    DateTime          CountryCode

AAAA               Failure     09-10-2018      US

AAAA               Success    09-11-2018      US

AAAA               Failure     10-10-2018       US

AAAA               Failure     10-11-2018       US

AAAA               Success   10-12-2018       US

BBB                  Failure     11-01-2018       DE

BBB                  Failure     11-02-2018       DE

BBB                 Success     11-03-2018      DE

 

Output  Required::

 

Serial Number RecentFailure Country Code

AAAA               2                    US

BBB                  2                    DE

 

AAAA is having 2 recent failures as the record on '09/11/2018' has Success status so we count from latest success of '10/12/2018' to '09/11/2018'

Also, for BBB we have only 2 recentfailure as  we can see no success record in past is there so we count all failure records.

Please guide me through this. Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Count "Failure" Records with recent status "Success" for each Serial Number.

@khannadivyeh 

 

try this small revision. i think it should fix the issue

 

Measure =
VAR Last_Date =
    MAX ( Table1[DateTime] )
VAR LastStatusIsSuccess =
    CALCULATE ( MAX ( Table1[Status] ), Table1[DateTime] = Last_Date ) = "Success"
VAR SecondLastSuccessDate =
    CALCULATE (
        MAX ( Table1[DateTime] ),
        Table1[DateTime] < Last_Date,
        Table1[Status] = "Success"
    )
RETURN
    SWITCH (
        TRUE (),
        LastStatusIsSuccess, SWITCH (
            FALSE (),
            ISBLANK ( SecondLastSuccessDate ), COUNTROWS (
                FILTER (
                    Table1,
                    Table1[DateTime] >= SecondLastSuccessDate
                        && Table1[Status] = "Failure"
                )
            ),
            COUNTROWS ( FILTER ( Table1, Table1[Status] = "Failure" ) )
        )
    )
5 REPLIES 5
Super User
Super User

Re: Count "Failure" Records with recent status "Success" for each Serial Number.

Hi @khannadivyeh 

 

That Dejavu feelingSmiley Wink

 

Try this MEASURE

It works with your sample data

 

Measure =
VAR Last_Date =
    MAX ( Table1[DateTime] )
VAR LastStatusIsSuccess =
    CALCULATE ( MAX ( Table1[Status] ), LASTDATE ( Table1[DateTime] ) ) = "Success"
VAR SecondLastSuccessDate =
    CALCULATE (
        MAX ( Table1[DateTime] ),
        Table1[DateTime] < Last_Date,
        Table1[Status] = "Success"
    )
RETURN
    SWITCH (
        TRUE (),
        LastStatusIsSuccess, SWITCH (
            FALSE (),
            ISBLANK ( SecondLastSuccessDate ), COUNTROWS (
                FILTER (
                    Table1,
                    Table1[DateTime] >= SecondLastSuccessDate
                        && Table1[Status] = "Failure"
                )
            ),
            COUNTROWS ( FILTER ( Table1, Table1[Status] = "Failure" ) )
        )
    )
khannadivyeh Frequent Visitor
Frequent Visitor

Re: Count "Failure" Records with recent status "Success" for each Serial Number.

@Zubair_Muhammad Thanks a lot Man for initial testing its working. Seriously you have a magical hand in for DAX. I will mark it as Solution once l test with all conditions.

I am new to DAX can you guide me where should I start to learn it.

khannadivyeh Frequent Visitor
Frequent Visitor

Re: Count "Failure" Records with recent status "Success" for each Serial Number.

@Zubair_Muhammad , Hi Zubair the above measure is giving LASTDATE function exception on the below input::

 

The reason I could find is  the 2nd Row with SerialNumber 'AAA' with DateTime '19-01-2018 06:00' and 2nd Last Row with Serial Number 'BBB' and dateTime '19-01-2018 07:00' is giving exception of LASTDATE function. 

Also,  each serial Number DateTime can have exact datetime with another serialNumber. Please try with below sample and let me know how to resolve this one. Thanks

 

SerialNumberStatusDateTimeCountryCode
AAASuccess18-01-2018 10:00US
AAAFailure19-01-2018 06:00US
AAAFailure20-01-2018 06:00US
AAASuccess21-01-2018 13:00US
BBBFailure17-01-2018 13:00US
BBBSuccess19-01-2018 07:00US
CCCFailure02-01-2018 04:00US
Super User
Super User

Re: Count "Failure" Records with recent status "Success" for each Serial Number.

@khannadivyeh 

 

try this small revision. i think it should fix the issue

 

Measure =
VAR Last_Date =
    MAX ( Table1[DateTime] )
VAR LastStatusIsSuccess =
    CALCULATE ( MAX ( Table1[Status] ), Table1[DateTime] = Last_Date ) = "Success"
VAR SecondLastSuccessDate =
    CALCULATE (
        MAX ( Table1[DateTime] ),
        Table1[DateTime] < Last_Date,
        Table1[Status] = "Success"
    )
RETURN
    SWITCH (
        TRUE (),
        LastStatusIsSuccess, SWITCH (
            FALSE (),
            ISBLANK ( SecondLastSuccessDate ), COUNTROWS (
                FILTER (
                    Table1,
                    Table1[DateTime] >= SecondLastSuccessDate
                        && Table1[Status] = "Failure"
                )
            ),
            COUNTROWS ( FILTER ( Table1, Table1[Status] = "Failure" ) )
        )
    )