Lookup first date if available

[Cristian_N]

Hello,

I have a table with all the messages. These messages are by row, with columns indicating if it's an inbound message or outbound. The messages are also within several conversations.

I am trying to create a new column that would indicate in the inbound message row the DateTime of the first outbound message if there was one. So, it must be filtered by Conversation_ID and if there's an outbound message after the inbound.

I tried creating a duplicate table and filter that one where Outbound_Messages = 1, and then creating this new column in the Messages table like below. But the result sometimes works and sometimes it's empty.

AnsweredTime = 
    IF('Messages'[Inbound_Messages] = 1,
    CALCULATE (
	FIRSTNONBLANK ( 'Messages (Outbound)'[DateTime], True()),
	Filter(
		'Messages (Outbound)', 'Messages'[conversation_id] = 'Messages (Outbound)'[conversation_id] &&
		'Messages (Outbound)'[DateTime] >= 'Messages'[DateTime])) , BLANK())

The data looks something like this and the desired outcome is in the "Answered_Time" column.

Please help to understand what I am doing wrong or if there's a better way to do it. If there's the possibility to have it all in one table (without having to create a table that is filtered by Outbound_messages = 1) then it would be even better.

Thank you for your help!

[daXtreme]

[First Outbound Time] = // calculated column, not a measure!
// In calculated columns one should in general never
// use CALCULATE. This is especially important in
// big fact tables and the reason being context transition
// that needs to happen on each and every row. You don't
// want that as it'll halt your calculation to a complete halt.
var CurrentConversationId = Messages[conversation_id]
var FirstOutboundTime =
    MINX(
        filter(
            Messages,
            Messages[Outbound_messages] = 1
            &&
            Messages[conversation_id] = CurrentConversationId
        ),
        Messages[DateTime]
    )
return
    FirstOutboundTime

[Cristian_N]

Hi @daXtreme 

Thank you for your help. 

I believe this structure will always give the MIN date of the same conversation. However, let's say that the last row from the screenshot also gets answered, I want the AnsweredTime to have for that last row the DateTime of the new reply, not the previous.

Hopefully it makes sense!

[tamerj1 · Accepted solution]

 @Cristian_N 

based on the goven samle data and the the exoected result please try

AnsweredTime =
VAR CurrentDateTime = 'Messages (Outbound)'[DateTime]
VAR CurrentInbound = 'Messages (Outbound)'[Inbound_Messages]
VAR CurrentIDTable =
    CALCULATETABLE (
        'Messages (Outbound)',
        ALLEXCEPT ( 'Messages (Outbound)', 'Messages (Outbound)'[conversation_id] )
    )
VAR OutboundTable =
    FILTER ( CurrentIDTable, 'Messages (Outbound)'[Outbound_Messages] = 1 )
VAR OutboundDateTime =
    MINX ( OutboundTable, 'Messages (Outbound)'[DateTime] )
RETURN
    IF (
        CurrentInbound = 1
            && CurrentDateTime < OutboundDateTime,
        OutboundDateTime
    )

[Cristian_N]

@tamerj1 

By adding in the filter that also the DateTime is higher or equal to CurrentDateTime var I believe it's working.

VAR OutboundTable =
    FILTER ( CurrentIDTable, 'Messages'[Outbound_Messages] = 1 && 'Messages'[DateTime]>= CurrentDateTime)

 Thank you for your help!

[Cristian_N]

Hi @tamerj1 

Thank you for your reply. With slight modification on the table names (instead of "Messages (Outbound)" it should be the table "Messages") looks like it's getting the result, but only for the first inbound message.

Is there a way to apply the logic to all inbound messages? You asked as well why the last row is blank, and that's because there was no outbound message after the last inbound.

But imagining that there is one, this would be the desired outcome:

[tamerj1]

Hi @Cristian_N 
Please try

AnsweredTime =
VAR CurrentDateTime = 'Messages (Outbound)'[DateTime]
VAR CurrentInbound = 'Messages (Outbound)'[Inbound_Messages]
VAR CurrentIDTable =
    CALCULATETABLE (
        'Messages (Outbound)',
        ALLEXCEPT ( 'Messages (Outbound)', 'Messages (Outbound)'[conversation_id] )
    )
VAR OutboundTable =
    FILTER ( CurrentIDTable, 'Messages (Outbound)'[Outbound_Messages] = 1 )
VAR MinOutboundDateTime =
    MINX ( OutboundTable, 'Messages (Outbound)'[DateTime] )
VAR MaxOutboundDateTime =
    MAXX ( OutboundTable, 'Messages (Outbound)'[DateTime] )
RETURN
    IF (
        CurrentInbound = 1
            && CurrentDateTime < MaxOutboundDateTime,
        MinOutboundDateTime
    )

[tamerj1]

Hi @Cristian_N 

why the result of the last row is bkank?