Forum Discussion
usomaraju
6 years agoHelper II
DateDiff with Conditions
Hi, Can someone help me out with the datediff measure with Conditions I need to calculate the datediff based on Response code i.e the first occurrence of 500 and the next occurrence of 200, that ...
- 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.
Anonymous
6 years agoNot applicable
Can you share plz your dataset? No as image.
Thanks
usomaraju
6 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 |