Forum Discussion

AlkatRaaZ's avatar
AlkatRaaZ
New Member
2 years ago
Solved

Incident Ticket Count

Hi,

I have a table with 3 columns. TICKET ID, open date and closed date. I need the count of tickets raised and closed on a particular date. 

 

 

I created a calendar tabled with the min(open date) to max(closed date). Changed data type to "date". Now I created a active relationship with open tickets and inactive relationship with closed tickets.

 

To get the counts I used the below measures:

Open Tickets = Count(TicketID)

Closed Ticket = Calculate([Open Tickets], USERELATIONSHIP('calendar table'[date], table[closed date])

 

Using this I'm still not able to achieve the output as per the screenshot. Rather when I add the measures to the table, the calendar date column disappears and only the overall count remains. 

 

 

Need Help
Tutorial Requests
  • DataNinja777's avatar
    DataNinja777
    2 years ago

    Hi AlkatRaaZ ,

    For your analysis, which requires examining the duration of open ticket statuses using multiple date fields (such as opening and closing dates), it is crucial to have your calendar table as a disconnected table to ensure accurate results. To achieve the desired outcome, please follow these steps:

    1. Remove the relationship between your calendar table and the fact table containing ticket opening and closing dates.

    2. Use a DAX formula like the one below to count the number of open tickets on a specific date.

    Open tickets = 
SUMX (
    'Table',
    IF (
        'Table'[Open Date] <= MAX ( calendar[Date] )
            && 'Table'[Closed Date] >= MAX ( calendar[Date] ),
        1,
        BLANK ()
    )
)

    The resultant output from a dummy data is as shown below:

    I have attached an example pbix file for your reference. 

    Best regards,

4 Replies

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Icon for Administrator rankAdministrator
    2 years ago

    Hello
    Why not try adding a condition in your table, a calculated column that evaluates per row whether a ticket is open or closed.
    Status = IF(ISBLANK(table[closing date]), "OPEN", "CLOSED")

    Then, in your tables you put the Status column as a row and generate a count.


    If this post helps, consider Accept it as the solution to help other members find you faster.

    • AlkatRaaZ's avatar
      AlkatRaaZ
      New Member
      2 years ago

      Hey, Thanks for the reply.

       

      I would have done that, but all the tickets are already closed and have a closed date as well. Hence ive been trying to relate the ticket id with open and close date to get the count, biut im not really sure how to do it. I'm pretty sure its a simple solution and feel like im making it complex. 

      • Syndicate_Admin's avatar
        Syndicate_Admin
        Icon for Administrator rankAdministrator
        2 years ago

        I understand, you must do a dynamic calculation, I detail an example:

        Model


        Dax Measure

        closed =
        VAR Selected Date = MAX(tddate[Date])

        RETURN
        CALCULATE(COUNT(Board[closing date]), Board[closing date] <= Selected Date)

        open =
        COUNT(Board[id]) - [closed]



        Result

        If this publication helpConsider Accept it as the solution to help the other members find you more quickly.


  • DataNinja777's avatar
    DataNinja777
    Icon for Super User rankSuper User
    2 years ago

    Hi AlkatRaaZ ,

    For your analysis, which requires examining the duration of open ticket statuses using multiple date fields (such as opening and closing dates), it is crucial to have your calendar table as a disconnected table to ensure accurate results. To achieve the desired outcome, please follow these steps:

    1. Remove the relationship between your calendar table and the fact table containing ticket opening and closing dates.

    2. Use a DAX formula like the one below to count the number of open tickets on a specific date.

    Open tickets = 
SUMX (
    'Table',
    IF (
        'Table'[Open Date] <= MAX ( calendar[Date] )
            && 'Table'[Closed Date] >= MAX ( calendar[Date] ),
        1,
        BLANK ()
    )
)

    The resultant output from a dummy data is as shown below:

    I have attached an example pbix file for your reference. 

    Best regards,