Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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
)
[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
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!
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
)
@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!
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:
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
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
14 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |