Forum Discussion

usomaraju's avatar
usomaraju
Helper II
6 years ago
Solved

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 ...
  • Greg_Deckler's avatar
    Greg_Deckler
    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
    __Flag

     

    The 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.