Forum Discussion

Dnssdrs's avatar
Dnssdrs
Icon for Microsoft Employee rankMicrosoft Employee
11 months ago
Solved

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 Tic...
  • BeaBF's avatar
    BeaBF
    11 months ago

    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!

  • Kedar_Pande's avatar
    Kedar_Pande
    11 months ago

    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

Kedar_Pande's avatar
Kedar_Pande
Icon for Super User rankSuper User
11 months ago

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