Summarize rows by time and result

[adinh]

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. 

[rajendraongole1 · Accepted solution]

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.

[rajendraongole1 · Accepted solution]

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]

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 · Accepted solution]

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.