Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

@Anonymous 

 

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" ) )
        )
    )

Regards
Zubair

Please try my custom visuals

View solution in original post

5 REPLIES 5
Zubair_Muhammad
Community Champion
Community Champion

Hi @Anonymous 

 

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" ) )
        )
    )

Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

@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.

Anonymous
Not applicable

@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

@Anonymous 

 

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" ) )
        )
    )

Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.