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...
  • 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
    )
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!