Forum Discussion

adinh's avatar
adinh
Frequent Visitor
1 year ago
Solved

Summarize rows by time and result

Hello!

 

I am trying to summarize a call log by pulling the row where the latest "Answered" result from the "Help Desk" operator occurs for each call number. If the call was not answered by anyone, the row with the latest timestamp of "No Answer" would be pulled. 

For example, the goal would be to pull row 457 from this call number. 

 

 

Need Help
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    Hi adinh - you can create a measure that identifies the latest timestamp for "Answered" , you can replace the table name as per your model.

    LatestAnsweredTimestamp =
    CALCULATE(
    MAX(CallLog[Timestamp]),
    FILTER(
    CallLog,
    CallLog[Result] = "Answered" && CallLog[Operator] = "Help Desk"
    )
    )

    create another measure for no answer as like above without help desk condition.

    LatestNoAnswerTimestamp =
    CALCULATE(
    MAX(CallLog[Timestamp]),
    FILTER(
    CallLog,
    CallLog[Result] = "No Answer"
    )
    )

     

    Now create last measure that chooses the row with the latest timestamp for "Answered" and, if no such result exists, defaults to "No Answer"

    FinalTimestamp =
    IF(
    ISBLANK([LatestAnsweredTimestamp]),
    [LatestNoAnswerTimestamp],
    [LatestAnsweredTimestamp]
    )

    now you  can use the TopN function in DAX to pull the latest entry

    TopNResult =
    TOPN(
    1,
    CallLog,
    [FinalTimestamp],
    DESC
    ) 

    it returns the latest answered. hope the above measure helps to achieve the same.

     

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi adinh 

     

    First of all, thank you rajendraongole1 for your positive reply!
    Change the DAX formula for the "TopNResult" in the method provided by rajendraongole1 as follows:

     

    TopNResult = 
SUMMARIZE(
    ADDCOLUMNS(
        CallLog,
        "FinalTimestamp", [FinalTimestamp]
    ),
    CallLog[NO.],
    "LatestTimestamp", MAXX(
        TOPN(
            1,
            FILTER(
                CallLog,
                CallLog[NO.] = EARLIER(CallLog[NO.])
            ),
            [FinalTimestamp],
            DESC
        ),
        [FinalTimestamp]
    ),
    "Operator", MAXX(
        TOPN(
            1,
            FILTER(
                CallLog,
                CallLog[NO.] = EARLIER(CallLog[NO.])
            ),
            [FinalTimestamp],
            DESC
        ),
        CallLog[Operator]
    ),
    "Result", MAXX(
        TOPN(
            1,
            FILTER(
                CallLog,
                CallLog[NO.] = EARLIER(CallLog[NO.])
            ),
            [FinalTimestamp],
            DESC
        ),
        CallLog[Result]
    )
)

     

     

     

    Best Regards,
    Jarvis Tang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

3 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    1 year ago

    Hi adinh - you can create a measure that identifies the latest timestamp for "Answered" , you can replace the table name as per your model.

    LatestAnsweredTimestamp =
    CALCULATE(
    MAX(CallLog[Timestamp]),
    FILTER(
    CallLog,
    CallLog[Result] = "Answered" && CallLog[Operator] = "Help Desk"
    )
    )

    create another measure for no answer as like above without help desk condition.

    LatestNoAnswerTimestamp =
    CALCULATE(
    MAX(CallLog[Timestamp]),
    FILTER(
    CallLog,
    CallLog[Result] = "No Answer"
    )
    )

     

    Now create last measure that chooses the row with the latest timestamp for "Answered" and, if no such result exists, defaults to "No Answer"

    FinalTimestamp =
    IF(
    ISBLANK([LatestAnsweredTimestamp]),
    [LatestNoAnswerTimestamp],
    [LatestAnsweredTimestamp]
    )

    now you  can use the TopN function in DAX to pull the latest entry

    TopNResult =
    TOPN(
    1,
    CallLog,
    [FinalTimestamp],
    DESC
    ) 

    it returns the latest answered. hope the above measure helps to achieve the same.

     

    • adinh's avatar
      adinh
      Frequent Visitor
      1 year ago

      HI rajendraongole1,
      This puts me in the right direction, but it seems to only track the latest call for the entire dataset instead of pulling the latest time for each call number (column NO.) 

      There should be a row for every call logged. Would the SUMMARIZE function work instead?

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi adinh 

         

        First of all, thank you rajendraongole1 for your positive reply!
        Change the DAX formula for the "TopNResult" in the method provided by rajendraongole1 as follows:

         

        TopNResult = 
SUMMARIZE(
    ADDCOLUMNS(
        CallLog,
        "FinalTimestamp", [FinalTimestamp]
    ),
    CallLog[NO.],
    "LatestTimestamp", MAXX(
        TOPN(
            1,
            FILTER(
                CallLog,
                CallLog[NO.] = EARLIER(CallLog[NO.])
            ),
            [FinalTimestamp],
            DESC
        ),
        [FinalTimestamp]
    ),
    "Operator", MAXX(
        TOPN(
            1,
            FILTER(
                CallLog,
                CallLog[NO.] = EARLIER(CallLog[NO.])
            ),
            [FinalTimestamp],
            DESC
        ),
        CallLog[Operator]
    ),
    "Result", MAXX(
        TOPN(
            1,
            FILTER(
                CallLog,
                CallLog[NO.] = EARLIER(CallLog[NO.])
            ),
            [FinalTimestamp],
            DESC
        ),
        CallLog[Result]
    )
)

         

         

         

        Best Regards,
        Jarvis Tang
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.