Forum Discussion
DateDiff with Conditions
- 6 years ago
Well, you have two problems here. First, you need to flag the correct 500 rows to do your calculation on. Second, you need to do your calculation.
The first part is something like this:
IsFirst500After200 = VAR __Table500 = FILTER(ALL('Table'),[Response code] = 500 && 'Table'[Index]<EARLIER('Table'[Index])) VAR __Table200 = FILTER(ALL('Table'),[Response code] = 200 && 'Table'[Index]<EARLIER('Table'[Index])) VAR __Max500 = MAXX(__Table500,[Index]) VAR __Max200 = MAXX(__Table200,[Index]) VAR __Flag = SWITCH(TRUE(), [Response code] = 200,FALSE(), __Max500 > __Max200,FALSE(), __Max500 < 'Table'[Index] && ISBLANK(__Max200),FALSE(), TRUE() ) RETURN __FlagThe second part:
Duration = IF( [IsFirst500After200], VAR __Next200 = MINX(FILTER(ALL('Table'),'Table'[Index] > EARLIER('Table'[Index]) && 'Table'[Response code] = 200),[Event time]) RETURN DATEDIFF([Event time],__Next200,SECOND), BLANK() )This second part is the identical technique used in MTBF. http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
I have attached the PBIX file.
Hi ,
The Query that you given did not work,it gives the null values.
here i'm posting my complete table, can you please help on this.
We are trying to calculate time difference between (first occurance of 500 - first occurance of 200) and continue the same for the next occurance of 500 and 200.
example:event time of index '0' - event time of index '4'
event time of index '8' - event time of index '9'
and so forth.
Can you share plz your dataset? No as image.
Thanks
- usomaraju6 years agoHelper II
Hi,
Here is the data not in the form of image
Event time Response code Request Name Index 2/22/2020 17:12:19 PM 500 POST Write/CaseSavedEvent 0 2/22/2020 17:13:19 PM 500 POST Write/CaseSavedEvent 1 2/22/2020 17:14:20 PM 500 POST Write/CaseSavedEvent 2 2/22/2020 17:15:46 PM 500 POST Write/CaseSavedEvent 3 2/22/2020 17:23:15 PM 200 POST Write/CaseSavedEvent 4 2/22/2020 17:24:15 PM 200 POST Write/CaseSavedEvent 5 2/22/2020 17:25:15 PM 200 POST Write/CaseSavedEvent 6 2/22/2020 17:26:15 PM 200 POST Write/CaseSavedEvent 7 2/22/2020 18.30:15 PM 500 POST Write/CaseSavedEvent 8 2/22/2020 18:33:15 PM 200 POST Write/CaseSavedEvent 9 2/22/2020 18:43:15 PM 500 POST Write/CaseSavedEvent 10 2/22/2020 18:44:15 PM 200 POST Write/CaseSavedEvent 11 2/22/2020 18:45:15 PM 200 POST Write/CaseSavedEvent 12