Hi,
Could you please help me to achieve the below scenario. it would be great if you could help on this Scenario.
Scenario: I need date difference between Inbound Created and Outbound Created date difference.
| CASE ID | Created on | Direction | Expected Date (Date Difference between First Inbound Created date should be update in first Outbound ) |
| 101 | 01-08-2022 11:30:00 | Inbound | |
| 101 | 01-08-2022 11:00:00 | Outbound | |
| 101 | 01-08-2022 9:40:00 | Outbound | 01-08-2022 9:30:00 |
| 101 | 01-08-2022 9:30:00 | Inbound | |
| 101 | 01-08-2022 10:20:00 | Outbound | 01-08-2022 8:00:00 |
| 101 | 01-08-2022 9:30:00 | Inbound | |
| 101 | 01-08-2022 8:00:00 | Inbound | |
| 101 | 01-08-2022 7:20:00 | Outbound | |
| 101 | 01-08-2022 6:30:00 | Outbound | |
| 101 | 01-08-2022 6:00:00 | Outbound | |
| 101 | 01-08-2022 6:00:00 | Outbound | |
| 102 | 02-05-2022 6:00:00 | Outbound | 02-05-2022 4:55:00 |
| 102 | 02-05-2022 4:55:00 | Inbound | |
| 102 | 02-05-2022 3:40:00 | Outbound | 02-05-2022 2:00:00 |
| 102 | 02-05-2022 2:00:00 | Inbound | |
| 103 | 02-05-2022 6:00:00 | Outbound | |
| 104 | 02-05-2022 6:00:00 | Inbound |
Could you please help on this scenario.
Thanks In Advance.
So I'm not finished, but am getting close.
As you will see, I really need an answer to the duplicate timestamps.
These present a significant problem.
Scenario A = CASE ID 102
1) Create the following "Order" column.
It is imperative that this column groups by both [CASE ID] & [Direction].
The purpose of this column is to label in order (from 1 to N) the lowest timestamp to the highest timestamp within each grouping of CASE ID & Direction.
NOTE: In this case, I am isolating CASE ID 102 to simplify the example.
2) Create a new table called "Inbound" as follows, including only the Inbound rows.
NOTE: I renamed column "Created On" to "Date In".
3) Create a new table called "Outbound" as follows, including only the Outbound rows.
4) In the Inbound table, create the following columns:
- Date Out
NOTE: THIS IS WHERE THE MAGIC HAPPENS! WE'RE LINKING [Date In] TO [Date Out] BASED ON [Order]!
- Difference DateDiff (in minutes)
- Difference Operator (in HH:MM:SS)
NOTES: I provided Difference in 2 different ways. Use whichever you like.
Scenario B = CASE ID 101
- As you can see in Line 10 & 11, there are 2 identical rows for CASE 101 & Direction Outbound. As a result, both rows get assigned Order #2. For this reason, no Order #1 gets created for 101 Outbound, and therefore, cannot match up correctly with Order #1 for 101 Inbound. The end result, is incorrect matching & incorrect calculations.
- There is a similar problem with Line 4 & 6. There are 2 identical rows for CASE 101 & Direction Inbound. I don't understand how Order selected Row 6 as "Order 1" & Row 4 as "Order 2". But this really doesn't matter since we don't know which one should come first anyway. So it could be right or wrong, but we have no way to know.
Finally, as you can tell, I have also not yet programmed in the rule about [Date In] needing to be LESS THAN [Date Out] for it to be valid. If you get stuck on this one, let me know and I will come back to you.
Before worrying about that though, you either need to:
1) Remove identical duplicate rows (OR)
2) Clearly specify how to handle identical duplicate rows, if they are in fact, legitimate.
Hope what I have provided so far is helpful to you!
Regards,
Nathan
Hello @karthickpbi -
- The new dataset you provided on Saturday (Case ID's: 101 & 1023) is significantly different from the original dataset provided (Case ID's: 101, 102, 103, 104). Since there are new requirements based on the new columns in the new dataset, I am abandoning the original datset in favor of the new one.
- For readability (and for easier column reference in DAX), I have shortened and/or renamed columns as follows:
- "To Recipients" => "Recipient"
- "Sender Email domain" => "Sender Domain"
- "Torecipient email domain" => "Recipient Domain"
- "Inbound Internal or External" => "Inbound Type"
- "Outbound Internal or External" => "Outbound Type"
- "Internal Email / External Email" => "Process"
- Regarding your post with 6 points, I want to make sure I'm understanding correctly.
1. We want to:
- Exclude WHERE [Process] LIKE '%Internal%' (NOT highlighted below)
- Include WHERE [Process] IN ('External Received', 'External Handled')
- (highlighted yellow below)
- Result: We only keep Gmail.com / Dell.com conversation
- dell to gmail (highlighted orange)
- gmail to dell (highlighted blue)
- FINAL RESULT: We only keep the rows marked in green!
- Keep only Lines: 3, 6, 7, 9, 13-17
- Exclude all other lines.
- Lines 18, 27-29 have [Process] LIKE '%External%'. However, they are EXCLUDED because the conversation is NOT between dell.com & gmail.com.
- Am I understanding everything correctly so far? Please make any corrections.
2. "INBOUND : Will always send from chandra@gmail.com to xjdkdirfmff@dell.com(Dell.com)"
- I don't see either of these email addresses in the new dataset.
- So perhaps this is another way of saying the conversation is between
@gmail.com & @dell.com? But this is already stated in Point #1.
- So I'm ignoring Point #2.
- Correct me if I'm misunderstanding.
3. "Outbound : will always send from xjdkdirfmff@dell.com(Dell.com) to chandra@gmail.com."
- I'm ignoring Point #3 for the same reason as Point #2.
- Correct me if I'm misunderstanding.
4."Note: In Inbound 90% will always Inbound sender Outbound of torecipient is same customer only (External(Gmail.com))"
- My apologies. I'm not sure what this means.
- Are you simply saying that 90% of the time, the Sender Domain for external emails will be @gmail.com?
- There is no filter criteria for this point, correct? It's just an FYI, right?
5. "Inbound = customer sending (Sender)email to agent (to receipient)"
- There is no filter criteria for this point, correct? It's just an FYI, right?
6 Outbound = agent sending (sender) email to customer sending (to receipient)
- There is no filter criteria for this point, correct? It's just an FYI, right?
Again, please make any corrections to my understanding of new requirements, as needed.
Regards,
Nathan
Thanks for your response. It's a relief to know there should be no duplicates!
So can you tell me exactly which Line #'s should be changed from AM to PM?
I will update my test data in the following screenshot accordingly.
Regards,
Nathan
So I'm not finished, but am getting close.
As you will see, I really need an answer to the duplicate timestamps.
These present a significant problem.
Scenario A = CASE ID 102
1) Create the following "Order" column.
It is imperative that this column groups by both [CASE ID] & [Direction].
The purpose of this column is to label in order (from 1 to N) the lowest timestamp to the highest timestamp within each grouping of CASE ID & Direction.
NOTE: In this case, I am isolating CASE ID 102 to simplify the example.
2) Create a new table called "Inbound" as follows, including only the Inbound rows.
NOTE: I renamed column "Created On" to "Date In".
3) Create a new table called "Outbound" as follows, including only the Outbound rows.
4) In the Inbound table, create the following columns:
- Date Out
NOTE: THIS IS WHERE THE MAGIC HAPPENS! WE'RE LINKING [Date In] TO [Date Out] BASED ON [Order]!
- Difference DateDiff (in minutes)
- Difference Operator (in HH:MM:SS)
NOTES: I provided Difference in 2 different ways. Use whichever you like.
Scenario B = CASE ID 101
- As you can see in Line 10 & 11, there are 2 identical rows for CASE 101 & Direction Outbound. As a result, both rows get assigned Order #2. For this reason, no Order #1 gets created for 101 Outbound, and therefore, cannot match up correctly with Order #1 for 101 Inbound. The end result, is incorrect matching & incorrect calculations.
- There is a similar problem with Line 4 & 6. There are 2 identical rows for CASE 101 & Direction Inbound. I don't understand how Order selected Row 6 as "Order 1" & Row 4 as "Order 2". But this really doesn't matter since we don't know which one should come first anyway. So it could be right or wrong, but we have no way to know.
Finally, as you can tell, I have also not yet programmed in the rule about [Date In] needing to be LESS THAN [Date Out] for it to be valid. If you get stuck on this one, let me know and I will come back to you.
Before worrying about that though, you either need to:
1) Remove identical duplicate rows (OR)
2) Clearly specify how to handle identical duplicate rows, if they are in fact, legitimate.
Hope what I have provided so far is helpful to you!
Regards,
Nathan
Thanks for providing all this information. Very helpful.
- I now understand that we map the FIRST-IN with FIRST-OUT for a given case, provided that [FIRST-IN timestamp] < [FIRST-OUT timestamp].
- Is it also true that we map SECOND-IN with SECOND-OUT for the same case?
- And THIRD-IN with THIRD-OUT for the same case?
- And so on to infiniti within the same case?
Points 2 & 5 are a bit confusing to me.
2. Case 101, Line no 7,6, 5 - in this case 7& 6 are Inbound, so we need to
- map 7 Inbound with 5 line for Outbound. (First, you say to map 5 to 7.)
- so need to map with 6 to 7. (Then, you say to map 6 to 7. Is this a typo?)
5. Case 102, we have one Inbound and outbound, one inbound and one outbound , we need to map first inbiund and first outbound.
- I notice that no reference is made to mapping the SECOND-IN to SECOND-OUT here (within Case 102), even though it exists (and it was also done for Case 101 on lines 3 & 4). Is this intentional? For example, the FIRST pair I have labeled in the following screenshot as "Map 3". The SECOND pair I have labled as "Map 4". Should Line 12 & 13 be marked as "N/A" or a valid map pair?
One other thing confuses me as well. In Case 101, Line 4 & 6 both have an identical timestamp of 1/8/22 9:30.
So for this SECOND-IN + SECOND-OUT pairing, how do we tell DAX which line to choose for the pairing?
Finally, could you review the entire "Map" column I added for all data; checking every single row & making corrections as necessary?
Regards,
Nathan
Helpful resources
Power BI Monthly Update - October 2024
Check out the October 2024 Power BI update to learn about new features.
Microsoft Fabric & AI Learning Hackathon
Learn from experts, get hands-on experience, and win awesome prizes.
Fabric Community Update - October 2024
Find out what's new and trending in the Fabric Community.
