Ticket Open and Closed DistinctCount

[ERIC_A]

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 ! 

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[ERIC_A]

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! 

[Ashish_Mathur]

You are welcome.