March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello,
I would like to count the monthly/weekly unresolved tickets , here is the data sample :
Category | created | resolutiondate |
AA | 11/10/2019 13:44:56 | null |
AA | 03/10/2019 16:55:17 | null |
AA | 06/09/2019 10:17:26 | 11/10/2019 14:17:08 |
BB | 08/10/2019 15:20:03 | null |
CC | 08/10/2019 14:46:50 | null |
AA | 08/10/2019 14:40:11 | null |
BB | 08/10/2019 08:34:12 | null |
CC | 07/10/2019 09:46:58 | null |
DD | 04/10/2019 11:58:14 | null |
AA | 03/10/2019 13:08:51 | null |
CC | 01/10/2019 11:31:47 | 01/10/2019 18:56:07 |
DD | 25/09/2019 17:39:51 | null |
BB | 30/09/2019 15:26:11 | 07/10/2019 17:57:11 |
BB | 27/09/2019 09:50:46 | null |
CC | 25/09/2019 16:36:14 | null |
AA | 24/09/2019 16:37:18 | 10/10/2019 15:42:18 |
The inprogress ticket is the one doesn't have the resolution date at the end of the month or the week or at the given date.
Please advise.
Thanks in advance.
Solved! Go to Solution.
Hi @Anonymous ,
Please use ALL instead of ALLSELECTED to have a try.If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.
Unresolved =CALCULATE (COUNTA(Bug[created]); FILTER( ALL(Bug); (Bug[resolutiondate] = BLANK() || Bug[resolutiondate]> Maxx (Calendrier;Calendrier[DateSlicer] )) && Bug[createddate]<=Maxx ( Calendrier; Calendrier[DateSlicer] ) ) )
Hi,
You may download my PBI file from here.
Hope this helps.
@Anonymous Please try this as a "New Table"
Test05Out = FILTER(SUMMARIZECOLUMNS(Test05[resolutiondate],"Count",COUNT(Test05[Category ])),Test05[resolutiondate]<>BLANK())
Use the resolutiondate as your date hierarchy column to view the counts by Month or Week
Proud to be a PBI Community Champion
Hi PattemManohar,
I use the Line and Clustered Column Chart, which has the created, closed tickets as column, and unresolved tickets as the line. I need to create a measure.
I tried this, but the amount of unresolved ticket changed when I filter the report by time.
Hi @Anonymous ,
Please use ALL instead of ALLSELECTED to have a try.If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.
Unresolved =CALCULATE (COUNTA(Bug[created]); FILTER( ALL(Bug); (Bug[resolutiondate] = BLANK() || Bug[resolutiondate]> Maxx (Calendrier;Calendrier[DateSlicer] )) && Bug[createddate]<=Maxx ( Calendrier; Calendrier[DateSlicer] ) ) )
@Anonymous Please create these two measures.
Open Tickets = COUNTROWS(FILTER(Test05,Test05[resolutiondate]=BLANK())) Closed Tickets = COUNTROWS(FILTER(Test05,Test05[resolutiondate]<>BLANK()))
Proud to be a PBI Community Champion
I need to create a measure, not a table. Thanks for your advise.
Proud to be a PBI Community Champion
Hi,
If I understand well, these closed and Open tickets are measure in the table
Test05Out
I already have 2 measures Open / Closed tickets, I would like to create another measure of the non resolved ticket. Let me know if It's clear for you or maybe I don't understand well 🙂
User | Count |
---|---|
122 | |
98 | |
89 | |
74 | |
67 |
User | Count |
---|---|
139 | |
115 | |
114 | |
98 | |
98 |