Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 !
Solved! Go to Solution.
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.
User | Count |
---|---|
77 | |
74 | |
62 | |
61 | |
45 |
User | Count |
---|---|
108 | |
100 | |
91 | |
83 | |
61 |