Earliest record - True/False

[Daven]

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 DateTime	Called from	Agent	Expected result - First call of the day from same Number
11/01/2019 11:21:20	123	A	True
11/01/2019 11:30:20	123	B	False
11/02/2019 10:20:10	123	A	True
11/01/2019 13:21:20	456	C	True
11/03/2019 14:20:10	456	C	True
11/04/2019 10:10:20	Blocked	A	True
11/04/2019 10:10:20	Blocked	C	False

Thanks,

Daven

[MartynRamsden]

Hi @Daven 

Should the expected result in Row 3 be TRUE?

Best regards,

Martyn

[Daven]

Hi,

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

I updated it.

Daven

[MartynRamsden · Accepted solution]

 Hi @Daven 

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.

[Daven]

Hi Martyn,

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

Hope you have a great day!

Best, 

Daven