Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Lookup first date if available

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!

 

 

  • tamerj1's avatar
    tamerj1
    4 years ago

     Anonymous 

    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
    )

7 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi Anonymous 

    why the result of the last row is bkank?

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

     Anonymous 

    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
    )
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        4 years ago

        Hi Anonymous 
        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
    )
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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!

  • daXtreme's avatar
    daXtreme
    Icon for Solution Sage rankSolution Sage
    4 years ago
    [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
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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!