identify missed calls

hi there, the requirement is that need to identify which missed calls were returned within 3 days. in the below, miss caller 1 call was returned on the 1st. so it has to count as a returned call...

Need Help

Anonymous

1 year ago

Hi, Lodewyk

Thanks your reply.

Please refer to the new DAX formula:

ReturnedStatus2 = 
SUMMARIZE (
    ADDCOLUMNS (
        FILTER (
            GENERATE (
                SELECTCOLUMNS (
                    FILTER ( 'Table1', CONTAINSSTRING ( 'Table1'[From], "miss caller" ) ),
                    "_To1", 'Table1'[To],
                    "_From1", 'Table1'[From],
                    "_result1", 'Table1'[Result],
                    "_Date1", 'Table1'[Date/Time]
                ),
                SELECTCOLUMNS (
                    FILTER ( 'Table1', CONTAINSSTRING ( 'Table1'[From], "receiver" ) ),
                    "_To2", 'Table1'[To],
                    "_From2", 'Table1'[From],
                    "_Date2", 'Table1'[Date/Time],
                    "_result2", 'Table1'[Result]
                )
            ),
            [_From1] = [_To2]
        ),
        "ReturnedWithin3Days",
            IF (
                DATEDIFF ( [_Date1], [_Date2], DAY ) <= 3
                    && [_result2] = "answered",
                "Returned",
                "No Returned"
            )
    ),
    [_From1],
    [ReturnedWithin3Days]
)

I have updated the new pbix file below, hope this helps.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

identify missed calls.pbix62 KB

Anonymous

Not applicable

1 year ago

Thanks for dharmendars007's concern about this issue.

Hi, Lodewyk

I am glad to help you.

Maybe you can create a calculation table for my DAX that you can refer to:

ReturnedStatus = 
FILTER(
    ADDCOLUMNS(
        SUMMARIZE('Table1', 'Table1'[From]),
        "ReturnedWithin3Days", 
        VAR CurrentCaller = 'Table1'[From]
        VAR MissedCallDateTime = 
            CALCULATE(
                MIN('Table1'[Date/Time]),
                FILTER('Table1', 'Table1'[From] = CurrentCaller && 'Table1'[Result] = "missed")
            )
        VAR AnsweredCallDateTime = 
            CALCULATE(
                MIN('Table1'[Date/Time]),
                FILTER('Table1', 'Table1'[To] = CurrentCaller && 'Table1'[Result] = "Answered" && 'Table1'[Date/Time] > MissedCallDateTime)
            )
        VAR TimeDifference = DATEDIFF(MissedCallDateTime, AnsweredCallDateTime, DAY)
        RETURN IF(NOT(ISBLANK(AnsweredCallDateTime)) && TimeDifference < 3, "Returned", "Not Returned")
    ),
    'Table1'[From] IN {"miss caller 1", "miss caller 2", "miss caller 3"}
)