Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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" ) ) )
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!
@Anonymous
can you share some sample data with expected results... i will try to help
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |