Power BI Dax to find the Reopen Count of Ticket.

[Anonymous]

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.

[Anonymous]

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