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 d...
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.

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Martyn,

     

    Thank you so much for your time and providing the solution. It works! 

     

    Hope you have a great day!

     

    Best, 

     

    Daven