Forum Discussion

Lodewyk's avatar
Lodewyk
Helper I
1 year ago
Solved

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

Anonymous's avatar
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"}
)

 

 


I have attached the pbix file for this example below, I hope it helps.

Lodewyk's avatar
Lodewyk
Helper I
1 year ago

dharmendars007 , please let me know if it is possible not to harcode "

 'Table1'[From] IN {"miss caller 1", "miss caller 2", "miss caller 3"}

 

Because "miss caller 1" represents a telephone number and there will be hundreds of telephone numbers in to the "From" column

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.