Forum Discussion
Ticket Open and Closed DistinctCount
Hello,
I have the following Table
| Ticket | Open Date | Closed Date |
| A | 1/1/2023 | 1/1/2023 |
| B | 1/1/2023 | 1/1/2023 |
| C | 2/1/2023 | 2/1/2023 |
| D | 2/4/2023 | 2/1/2023
|
| E | 12/31/2022 | 1/1/2023 |
| F | 2/1/2023 | 2/1/2023 |
This table1 is connected to a calendar table for Open Date (active) Closed Date (not active).
I need to make the total count of tickets open + tickets closed on a specific date. The problem I have is that I don't know how to make a distinct count of my total open and total closed formula. Details below :
| Tickets opened on 01/01/2023 | |
| 2 | A,B |
| Tickets Closed on 01/01/2023 | |
| 3 | A,B,E |
| Tickets opened/closed on 01/01/2023 | |
| 5 | A,B,A,B,E |
| Expected | |
| 3 | A,B,E |
How can I make a formula to sum tickets opened +tickets closed and then make the distinctcount out of it?
DAX I have
totalClosed = calculate(DISTINCTCOUNT('Data'[ticket]),USERELATIONSHIP('Calendar'[Date],'Data'[Closed Date]))
totaltickets = distinctcount('Data'[ticket]')+totalClosed
Thank you !
Hi,
PBI file attached.
Hope this helps.
3 Replies
Hi,
PBI file attached.
Hope this helps.
hi Ashish thank you for your PBIX. your approach is interesting and is similar of what I did yesterday.
here is how I solved it .
I created a union table with the tickets and open date + tickets and closed date.
Open+Closed = union(
SELECTCOLUMNS('Data',"Ticket",'Data'[Incident],"Date",'Data'[Tkt Crtn Date]),
SELECTCOLUMNS('Data',"Ticket",'Data'[Incident],"Date",'Data'[SolutionAppliedDate]))and then I used this to obtain the distinct counts of tickets for a specific date.
thank you for your response!
You are welcome.
