Forum Discussion
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 means the Time diff between Index ‘0’ row and index ‘4’ row (2/22/2020 17:23 - 2/22/2020 17:12 = 11 min)
| Event time | Response code | Request Name | Index |
| 2/22/2020 17:12 | 500 | POST Write/CaseSavedEvent | 0 |
| 2/22/2020 17:13 | 500 | POST Write/CaseSavedEvent | 1 |
| 2/22/2020 17:14 | 500 | POST Write/CaseSavedEvent | 2 |
| 2/22/2020 17:15 | 500 | POST Write/CaseSavedEvent | 3 |
| 2/22/2020 17:23 | 200 | POST Write/CaseSavedEvent | 4 |
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.
6 Replies
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.
Well, this seems like an issue that would involve EARLIER. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490.
Also, if you want the difference in days, you can always just subtract the dates and multiply by 1.
