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.
I have a dataset which looks like as below. So this whole table is all about journey of a Ticket which is column (number), We have the (Reopen Count) column which tells how many times this ticket is reopen.
This whole table is sorted based on JourneyIndex.
1. Now if the Reopen count is 2 that means in the (value) column there will be 3 times resolved coming, This is because 2 times ticket is reopened but after the last resolved which is the 3rd here it is not open yet.
For ex. If the reopen will be 5 then there will be 6 times resolved will come in value column this is because customer get the solution in the 6th resolved and we can count only 5 as the reopen.
2. Whereever the resolved comes look for two conditions.
a. Is there any other resolved below to it, if not then it is fine.
b. if yes than look for the (Journey Category) column and whichever is the Assignment Group update, the no of reopen should show in front of it.
Based on Below data value JourneyCategory Reopen by Asssignment group
CSC-SPO-eBusiness Portal Supp Assignment Group Update 1
GT-USDG Prod Support - US Assignment Group Update 0
GT-Digital EEBenefit - US Assignment Group Update 1
(CSC-SPO-eBusiness Portal Supp ) is having 1 as (Reopen by Asssignment group) because below to it only 1 resolved in the value column is there.
(GT-USDG Prod Support - US) is having 0 as (Reopen by Asssignment group) because below to it no resolved in the value column is there.
(GT-Digital EEBenefit - US) is having 1 as (Reopen by Asssignment group) because below to it 2 resoved is there but the 2nd resoved is the last resolved in the value column so it will not be counted as Reopen.
whereever is the first Assignment Group Update comes in Journey Category column just count the no. of resolved below to the Assignment Group Update and before the next Assignment Group Update and the count should show in front of the Assignment Group Update in a new column.
For example : In the below table there is Reopen Count column which is the result I want through DAX.
In below table the first Assignment Group Update is coming at journeyIndex 5 and next Assignment Group Update is at journeyIndex 12. Now look resolved in value column in between 5 and 12 it is ony one time coming so count of resoved should show in Reopen Count coumn which is 1.
Next is 0 because between 12 index and 16 index there is no resolved in value column.
At last between 16 to 24 index 2 times resolved is coming but Since after last resolved ticket is not open again so we will not include last resolved count in reopen count that is why in last Assignment Group Update one there in reopent count.
Note: Lot of Dax expert have tried this problem but failed to achieve the result, I know problem is really hard but I want to also try here to see whether this community is really having expert or not.
number | timestamp | value | journeyIndex - | journeyCategoryIndex | journeyCategory | Reopen Count | Reopen Count |
INC010847428 | 9/27/2023 4:01:00 PM | Opened | 0 | 0 | State Update | 2 | |
INC010847428 | 9/27/2023 4:01:00 PM | Created | 1 | 1 | State Update | 2 | |
INC010847428 | 9/27/2023 4:02:00 PM | 3 | 2 | 0 | Priority Update | 2 | |
INC010847428 | 9/27/2023 4:02:00 PM | Active | 3 | 2 | State Update | 2 | |
INC010847428 | 9/27/2023 4:02:00 PM | 3 | 4 | 1 | Priority Update | 2 | |
INC010847428 | 9/27/2023 4:02:00 PM | CSC - SPO - eBusiness Portal Supp | 5 | 0 | Assignment Group Update | 2 | 1 |
INC010847428 | 9/28/2023 11:03:00 AM | Work in progress | 6 | 3 | State Update | 2 | |
INC010847428 | 9/28/2023 11:04:00 AM | Lorrie Smith | 7 | 0 | Assigned To Update | 2 | |
INC010847428 | 9/28/2023 11:09:00 AM | Pending | 8 | 4 | State Update | 2 | |
INC010847428 | 9/28/2023 11:36:00 AM | Resolved | 9 | 5 | State Update | 2 | |
INC010847428 | 9/29/2023 9:30:00 AM | Active | 10 | 6 | State Update | 2 | |
INC010847428 | 10/2/2023 8:26:00 AM | Work in progress | 11 | 7 | State Update | 2 | |
INC010847428 | 10/2/2023 9:25:00 AM | GT - USDG Prod Support - US | 12 | 1 | Assignment Group Update | 2 | 0 |
INC010847428 | 10/2/2023 9:25:00 AM | Active | 13 | 8 | State Update | 2 | |
INC010847428 | 10/2/2023 9:28:00 AM | Ashish Konapure | 14 | 1 | Assigned To Update | 2 | |
INC010847428 | 10/2/2023 9:28:00 AM | Work in progress | 15 | 9 | State Update | 2 | |
INC010847428 | 10/2/2023 9:43:00 AM | GT - Digital EEBenefit - US | 16 | 2 | Assignment Group Update | 2 | 1 |
INC010847428 | 10/2/2023 9:43:00 AM | Active | 17 | 10 | State Update | 2 | |
INC010847428 | 10/2/2023 10:56:00 AM | Carrie Griffin | 18 | 2 | Assigned To Update | 2 | |
INC010847428 | 10/2/2023 10:56:00 AM | Work in progress | 19 | 11 | State Update | 2 | |
INC010847428 | 10/2/2023 10:59:00 AM | Resolved | 20 | 12 | State Update | 2 | |
INC010847428 | 10/2/2023 11:16:00 AM | Active | 21 | 13 | State Update | 2 | |
INC010847428 | 10/2/2023 11:54:00 AM | Work in progress | 22 | 14 | State Update | 2 | |
INC010847428 | 10/2/2023 11:54:00 AM | Resolved | 23 | 15 | State Update | 2 | |
INC010847428 | 10/7/2023 12:05:00 PM | Closed | 24 | 16 | State Update | 2 |
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 | |
26 | |
22 | |
13 | |
8 |
User | Count |
---|---|
75 | |
50 | |
47 | |
17 | |
17 |