The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi
I want your help to get below requirment report :
1-Number of repeated Tickets ( more than 1 time for customer name) within resoulations date ( month ) and close root cause is repeated more than 1 time within same month
2- Monthly total count of repeated incidents percentage from total incident
Demo Data :
Ticketnumber | Service Name | Product Name | Resolution Date | Customer Name | Circuit Id | Root Cause |
334657 | Remot | Remot | 9/8/2019 11:21 | Test1 | Test1 | Power |
334694 | Remot | Remot | 9/1/2019 9:21 | Test1 | Test1 | Power |
335155 | Remot | Remot | 8/2/2019 22:04 | Test1 | Test1 | Power |
335322 | Remot | Remot | 8/5/2019 22:12 | Test1 | Test1 | Cute |
336292 | Remot | Remot | 9/3/2019 19:34 | Test2 | Test2 | Cute Cute |
336307 | Remot | Remot | 9/1/2019 13:44 | Test2 | Test2 | Power |
336310 | Remot | Remot | 9/2/2019 6:20 | Test2 | Test2 | power |
336335 | Remot | Remot | 9/1/2019 14:25 | Test2 | Test2 | Cute Cute |
336351 | Remot | Remot | 9/3/2019 15:59 | Test3 | Test3 | Cute Cute |
336374 | Remot | Remot | 9/1/2019 20:51 | Test2 | Test2 | Power |
Report will be :
Customer Name | Month | Count of repeat | Repeated root cause |
Test1 | Sep | 1 | Power |
Test2 | Sep | 2 | Power |
Solved! Go to Solution.
This logic seems to be based on circuit ID, not the root cause. I changed it. Also, created an additional measure to show data only when sum >1
https://www.dropbox.com/s/ino5zbpymbnj81y/RepeatFlag.pbix?dl=0
create as new column
last date of same issue in this month =
maxx(filter(table,table[Customer Name]=earlier(table[Customer Name]) && table[Root Cause]=earlier(table[Root Cause])
&& table[Resolution Date]<earlier(table[Resolution Date]) && month(table[Resolution Date]) =month(earlier(table[Resolution Date]))),table[Resolution Date])
repeat flag =
=
if(isblank(maxx(filter(table,table[Customer Name]=earlier(table[Customer Name]) && table[Root Cause]=earlier(table[Root Cause])
&& table[Resolution Date]<earlier(table[Resolution Date]) && month(table[Resolution Date]) =month(earlier(table[Resolution Date]))),table[Resolution Date])
),0,1)
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin
Okay it's work now but this give me date only of last issue not count of repeat time as example i want :
Customer Name | Month | Count of repeat | Repeated root cause |
Test1 | Sep | 2 | Power |
Test2 | Sep | 2 | Power |
first tickts will count as 0, not repeat but next tickts on same month will be 1 repeat
So if you are getting a date and as per Logic, I assume you getting only in the same month(In new column) . You can put if not isblank then 1 else 0 .
Then sum it up in visual.
I think I suggested a flag too. Is it giving wrong answer using flag ?
Yes Amitchandak, your solutions i think it can be work after this edit, how can i solve this problem please
@amitchandak thanks for your support
Please share what you are getting after the formula and what is expected. I think I need more information to help you out.
Also share the formula you are using
Hi @amitchandak
I am getting this now :
Customer Name | Month | Root cause | last date of the same issue in this month | repeat flag |
Test1 | Dec | Power | 12/1/2019 11:50:29 AM | Sunday, December 31, 1899 |
Test1 | Dec | Power | 12/1/2019 11:50:29 AM | Sunday, December 31, 1899 |
Forumla :
1-
last date of same issue in this month =
maxx(filter(Tickts,Tickts[Circuit Id]=EARLIER(Tickts[Circuit Id]) && Tickts[Root Cause]=earlier(Tickts[Root Cause])
&& Tickts[Resolution Date]<earlier(Tickts[Resolution Date]) && month(Tickts[Resolution Date])=month(earlier(Tickts[Resolution Date]))), Tickts[Resolution Date])
2-
repeat flag = if(isblank(maxx(filter(Tickts,Tickts[Customer Name]=earlier(Tickts[Customer Name]) && Tickts[Root Cause]=earlier(Tickts[Root Cause])
&& Tickts[Resolution Date]<earlier(Tickts[Resolution Date]) && month(Tickts[Resolution Date]) =month(earlier(Tickts[Resolution Date]))),Tickts[Resolution Date])
),0,1)
Needed Report :
Customer Name | Month | Root cause | Count of repeated time for Dec month with the same root cause | repeat flag |
Test1 | Dec | Power | 3 | Repeated if more than 1 time in the same month |
Test2 | Sep | Power | 4 | Repeated if more than 1 time in the same month |
Based on the initial data you provided, I create this pbix : https://www.dropbox.com/s/ino5zbpymbnj81y/RepeatFlag.pbix?dl=0
Please check and let me know any changes in the logic you required.
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin
Hi @amitchandak
I download file and apply forumla, but it give me 1 or 0 resoult, and some case it's not repated and give me 1
What i need, is number of repeated incidents or complines customer has open within same month, example below :
Customer Name | Month | Root cause | number of repeated times for Dec month with the same root cause |
Test1 | Dec | Power issue | 3 |
Test2 | Sep | Power issue | 4 |
Thanks a lot for you help
Is it possible for you to share data along with results after removing sensitive information?
Thanks.
My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
Hi @amitchandak
Below are the report after apply forumla,
Ticketnumber | Product Name | Resolution Date | Customer Name | Circuit Id | Incident Category | Root Cause | Repeat Count Sheet1 |
334657 | Water | 9/8/2019 11:21:44 AM | Test1 | QMDIA57 | Circuit Down | Faulty Router | 0 |
334694 | Water | 9/1/2019 9:21:22 AM | Test1 | QMDIA57 | Circuit Down | Circuit Under Migration | 0 |
335155 | Water | 9/2/2019 10:04:27 PM | Test1 | QMDIA57 | Circuit Down | Faulty Modem | 1 |
335322 | Water | 9/5/2019 10:12:58 PM | Test1 | QMDIA57 | Circuit Down | External Cabling Issue | 1 |
336292 | Water | 9/3/2019 7:34:17 PM | Test1 | QMDIA57 | Circuit Down | Connectivity issue | 1 |
336307 | Water | 9/1/2019 1:44:30 PM | Test2 | BMDIA289 | Circuit Down | Device Hanged | 0 |
336310 | Water | 9/2/2019 6:20:10 AM | Test1 | ABANET103 | Circuit Down | Connectivity issue | 1 |
336335 | Water | 9/1/2019 2:25:23 PM | Test1 | ABANET103 | Circuit Down | External Cabling Issue | 0 |
336351 | Water | 9/3/2019 3:59:52 PM | Test1 | BMDIA289 | Circuit Down | Connectivity issue | 1 |
336374 | Water | 9/1/2019 8:51:06 PM | Test1 | ABANET103 | Circuit Down | Connectivity issue | 0 |
336375 | Water | 9/2/2019 3:08:28 PM | Test1 | BMDIA289 | Circuit Down | Customer Internal Issue | 0 |
336414 | Water | 9/9/2019 5:14:12 AM | Test1 | QAMDIA62 | Circuit Down | External Cabling Issue | 1 |
336559 | Water | 9/1/2019 8:46:10 PM | Test3 | QAIPMW22 | Circuit Down | Connectivity issue | 0 |
336720 | Water | 9/2/2019 1:24:41 PM | Test1 | MMADH68 IP9 | Circuit Down | Faulty Modem | 0 |
336761 | Water | 9/3/2019 2:59:58 PM | Test1 | ABANET103 | Circuit Down | Connectivity issue | 1 |
This is a bit different from the last one. What is the expected output on this data.
Hi @amitchandak
it's the same but i add more data to be more clear
output data will be like this :
Customer Name | Circuit Id | Count of Circuit Id | Root Cause (Repeated more than 1 time) | Count of repeated) | Month |
Test1 | ABANET103 | 4 | Connectivity issue | 3 | Sep |
Test1 | BMDIA289 | 3 | No repeated root cause more than 1 time | No repeated root cause more than 1 time | No repeat |
Test1 | MMADH68 IP9 | 1 | No repeated root cause more than 1 time | No repeated root cause more than 1 time | No repeat |
This logic seems to be based on circuit ID, not the root cause. I changed it. Also, created an additional measure to show data only when sum >1
https://www.dropbox.com/s/ino5zbpymbnj81y/RepeatFlag.pbix?dl=0
Great Job @amitchandak this what i need, only i missing second filter for root cause
if root cause is repeated more than 1 time for the repeated Circute count as repeated only if root cause is repeated
Thanks a lot for your answe, when i apply this, i got below massage :
Cannot convert value 'December' of type Text to type Date.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
93 | |
81 | |
63 | |
56 |
User | Count |
---|---|
247 | |
122 | |
110 | |
77 | |
72 |