Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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" ) ) ) )
Hi @Anonymous
That Dejavu feeling
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" ) ) ) )
@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.
Thanks@Anonymous
Books are great to start with.
I found these books very useful
This one is great to start with for DAX
https://www.amazon.com/Power-Pivot-BI-Excel-2010-2016/dp/1615470395/ref=sr_1_3?keywords=Power+BI&qid=1555490370&s=books&sr=1-3
Then to up the ante
https://www.amazon.com/Definitive-Guide-DAX-intelligence-Microsoft/dp/1509306978/ref=sr_1_3?keywords=DAX&qid=1555490594&s=books&sr=1-3
For M /Power Query
@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
SerialNumber | Status | DateTime | CountryCode |
AAA | Success | 18-01-2018 10:00 | US |
AAA | Failure | 19-01-2018 06:00 | US |
AAA | Failure | 20-01-2018 06:00 | US |
AAA | Success | 21-01-2018 13:00 | US |
BBB | Failure | 17-01-2018 13:00 | US |
BBB | Success | 19-01-2018 07:00 | US |
CCC | Failure | 02-01-2018 04:00 | US |
@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" ) ) ) )
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |