Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Community,
This one has had me scratching my head for a while now and hoping someone can help. Similar to other posts and requirements for "open tickets", I'm looking to find a snapshot of my data at the first of each month, when the data fulfils criteria:
Given a table such as this:
Ticket Number | Team | Ticket Age | Type | Opened | Closed |
1 | A | 17 | High | 01-Apr-19 | 18-Apr-19 |
2 | A | 64 | High | 30-Apr-19 | |
3 | B | 45 | High | 19-May-19 | |
4 | B | 1 | High | 20-Jun-19 | 21-Jun-19 |
5 | A | 12 | Medium | 21-Jun-19 | |
6 | B | 39 | High | 22-May-19 | 30-Jun-19 |
7 | A | 1 | High | 23-Jun-19 | 24-Jun-19 |
8 | B | 9 | Low | 24-Jun-19 | |
9 | C | 1 | High | 25-Jun-19 | 26-Jun-19 |
10 | C | 1 | High | 26-Jun-19 | 27-Jun-19 |
Problem: I need to find, at the start of each month (01-MMM-YY), how many tickets of a certain type were open and over a certain number of days old, per team.
I can find something similar by adding a column to my date table, but this gives me a headline number I can't break down by team, as below:
Count =
CALCULATE (
COUNTA ( Records[Ticket Number] ),
FILTER (Records,
Records[Type] = "High"
&& Records[Opened] <= 'Date'[Date]
&& (Records[Closed] > 'Date'[Date] || ISBLANK ( Records[Closed] ) )
&& Records[Ticket Age] >= 10
)
)
Hopefully I'd be able to find something similar to this output, either as a new table, column, measure etc - but crucially, filterable by Team, because Team is linked to other dimensions I require:
Team | Month | Count |
A | 01-Apr | |
A | 01-May | 1 |
A | 01-Jun | 1 |
A | 01-Jul | 2 |
B | 01-Apr | |
B | 01-May | |
B | 01-Jun | 2 |
B | 01-Jul | 1 |
C | 01-Apr | |
C | 01-May | |
C | 01-Jun | |
C | 01-Jul |
I've attempted using SUMMARIZECOLUMNS but haven't had much success, any chance some DAX ninjas are able to assist?
Thanks in advance9i,
Ben
Problem: I need to find, at the start of each month (01-MMM-YY), how many tickets of a certain type were open and over a certain number of days old, per team.
Could you please clarify more about "over a certain number of days old"?
Regards,
Jimmy Tao
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |