topic Re: Lookup first date if available in DAX Commands and Tips

Lookup first date if available

Anonymous — Mon, 29 Aug 2022 15:24:00 GMT

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!

Re: Lookup first date if available

tamerj1 — Mon, 29 Aug 2022 18:57:44 GMT

Hi Anonymous

why the result of the last row is bkank?

Re: Lookup first date if available

tamerj1 — Mon, 29 Aug 2022 19:14:28 GMT

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 )

Re: Lookup first date if available

daXtreme — Mon, 29 Aug 2022 19:18:55 GMT

[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

Re: Lookup first date if available

Anonymous — Tue, 30 Aug 2022 13:26:53 GMT

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!

Re: Lookup first date if available

Anonymous — Tue, 30 Aug 2022 13:38:18 GMT

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:

Re: Lookup first date if available

Anonymous — Tue, 30 Aug 2022 13:49:32 GMT

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!

Re: Lookup first date if available

tamerj1 — Tue, 30 Aug 2022 13:50:12 GMT

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 )