Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Earliest record - True/False

Hi,

 

I have a table in below format and want to see the first call of the day from the same number. 

 

Please note there can be more than one call from "blocked number" at same time anwered by different agents. In that case sort by agent name

 

Called DateTimeCalled fromAgent Expected result - First call of the day from same Number
11/01/2019 11:21:20123

A

True
11/01/2019 11:30:20123BFalse
11/02/2019 10:20:10123ATrue
11/01/2019 13:21:20456CTrue
11/03/2019 14:20:10456CTrue
11/04/2019 10:10:20BlockedATrue
11/04/2019 10:10:20Blocked CFalse

 

Thanks,

 

Daven

Need Help
  • MartynRamsden's avatar
    MartynRamsden
    6 years ago

     Hi Anonymous 

     

    Add this as a calculated column:

    First Call = 
VAR RowDateTime = Table1[Called DateTime]
VAR RowDate = TRUNC ( Table1[Called DateTime] )
VAR RowCalledFrom = Table1[Called from]
VAR RowAgent = Table1[Agent ]
VAR FirstCallTime =
CALCULATE (
    MIN ( Table1[Called DateTime] ),
    FILTER (
        ALL ( Table1 ),
        TRUNC ( Table1[Called DateTime] ) = RowDate
            && Table1[Called from] = RowCalledFrom
    )
)
VAR FirstAgent = 
CALCULATE (
    MIN ( Table1[Agent ] ),
    FILTER ( 
        ALL ( Table1 ),
        Table1[Called DateTime] = RowDateTime
            && Table1[Called from] = RowCalledFrom
    )
)
VAR Result = 
SWITCH (
    TRUE(),
    RowCalledFrom <> "Blocked" && RowDateTime = FirstCallTime, TRUE(),
    RowCalledFrom = "Blocked" && RowDateTime = FirstCallTime && RowAgent = FirstAgent, TRUE(),
    FALSE()
)
RETURN Result

     

    Best regards,

    Martyn

     

    If I answered your question, please help others by accepting it as a solution.

4 Replies

  • MartynRamsden's avatar
    MartynRamsden
    Solution Sage
    6 years ago

    Hi Anonymous 

     

    Should the expected result in Row 3 be TRUE?

     

    Best regards,

    Martyn

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi,

       

      Yes the row 3 should be true. Thanks for catching it.

      I updated it.

       

      Daven

       

      • MartynRamsden's avatar
        MartynRamsden
        Solution Sage
        6 years ago

         Hi Anonymous 

         

        Add this as a calculated column:

        First Call = 
VAR RowDateTime = Table1[Called DateTime]
VAR RowDate = TRUNC ( Table1[Called DateTime] )
VAR RowCalledFrom = Table1[Called from]
VAR RowAgent = Table1[Agent ]
VAR FirstCallTime =
CALCULATE (
    MIN ( Table1[Called DateTime] ),
    FILTER (
        ALL ( Table1 ),
        TRUNC ( Table1[Called DateTime] ) = RowDate
            && Table1[Called from] = RowCalledFrom
    )
)
VAR FirstAgent = 
CALCULATE (
    MIN ( Table1[Agent ] ),
    FILTER ( 
        ALL ( Table1 ),
        Table1[Called DateTime] = RowDateTime
            && Table1[Called from] = RowCalledFrom
    )
)
VAR Result = 
SWITCH (
    TRUE(),
    RowCalledFrom <> "Blocked" && RowDateTime = FirstCallTime, TRUE(),
    RowCalledFrom = "Blocked" && RowDateTime = FirstCallTime && RowAgent = FirstAgent, TRUE(),
    FALSE()
)
RETURN Result

         

        Best regards,

        Martyn

         

        If I answered your question, please help others by accepting it as a solution.