Forum Discussion
COUNTROWS using date problem
I’m trying to create a weekly trend that tracks my job’s issue tickets — specifically the ‘Created Ticket’ and ‘Closed Ticket’ for that week.
I’ve successfully created a measure for the ‘Created Ticket’, but I’m unable to use the same formula to create the ‘Closed Ticket’ measure.
For background, I have two tables: the First is my calendar table, and the Second contains issue titles, details, created dates, and closed dates for the tickets.
I’ve created a relationship (One-to-Many) from the first table’s ‘Date’ column to the second table’s ‘Created Ticket’ column.
Here’s what my trend looks like right now:
The ‘Closed Issue’ trend is displayed incorrectly; it should show a higher value and on the most X-axis.
Does anyone have any suggestions?
Dnssdrs Hi!
Which are your measures? Have you tried with:
Closed Tickets =
CALCULATE(
COUNTROWS(Tickets),
USERELATIONSHIP(Calendar[Date], Tickets[Closed Date])
)You can also try with:
Closed Tickets =
COUNTROWS(
FILTER(
Tickets,
Tickets[Closed Date] >= MIN(Calendar[Date]) &&
Tickets[Closed Date] <= MAX(Calendar[Date])
)
)BBF
💡 Did I answer your question? Mark my post as a solution!
👍 Kudos are appreciated
🔥 Proud to be a Super User!
Your relationship is wrong. You linked Calendar to Created Date, but Closed Date needs its own relationship.
Create an inactive relationship from Calendar[Date] to your tickets table[Closed Date]. Then use this measure:
Closed Tickets =
CALCULATE(
COUNTROWS(Tickets),
USERELATIONSHIP(Calendar[Date], Tickets[Closed Date])
)Use this measure in your trend line.
If this answer helped, please click Kudos or mark as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pande
2 Replies
Dnssdrs Hi!
Which are your measures? Have you tried with:
Closed Tickets =
CALCULATE(
COUNTROWS(Tickets),
USERELATIONSHIP(Calendar[Date], Tickets[Closed Date])
)You can also try with:
Closed Tickets =
COUNTROWS(
FILTER(
Tickets,
Tickets[Closed Date] >= MIN(Calendar[Date]) &&
Tickets[Closed Date] <= MAX(Calendar[Date])
)
)BBF
💡 Did I answer your question? Mark my post as a solution!
👍 Kudos are appreciated
🔥 Proud to be a Super User!
Your relationship is wrong. You linked Calendar to Created Date, but Closed Date needs its own relationship.
Create an inactive relationship from Calendar[Date] to your tickets table[Closed Date]. Then use this measure:
Closed Tickets =
CALCULATE(
COUNTROWS(Tickets),
USERELATIONSHIP(Calendar[Date], Tickets[Closed Date])
)Use this measure in your trend line.
If this answer helped, please click Kudos or mark as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pande