Ticket System need to account for open and closed ticket dates

[Anonymous]

Hi all, 

I am working on a ticket system report in which I have a ticket open date column and a closed date column. 

My report is set up with the connection between my date table on the ticket open date column. However, I realised that this results in incorrect figures for the number of tickets closed each date. 

I have created the following measure in order to change the relationship for calculating the amount of closed tickets by date, however, this is still not giving me the desired result. 

Closed Tickets =

CALCULATE (

COUNTROWS( 'Tickets') ,

FILTER (

ALL ( Ticket[TicketStatusName] ),

'Ticket'[Ticket_StatusName] = "Closed"

),

USERELATIONSHIP( 'DateTable'[Date], 'Ticket'[Ticket_CloseDate] )

)

I need to get the number of tickets opened and closed on specific dates. Any help is greatly appreciated. Thank you.

[Anonymous]

Hi @Anonymous ,

If I understand correctly, the issue is that you want to get the number of tickets opened and closed. Please try the following methods and check if they can solve your problem:

1.Ensure that in the data model, the Date Table[Date] is correctly related to the both Ticket[Ticket_OpenDate] and Ticket[Ticket_CloseDate].

2.Adjust the measure to use the correct relationships.

Opened Tickets = 
CALCULATE(
    COUNTROWS('Tickets'),
    'Tickets'[TicketStatusName] <> "Closed",
    USERELATIONSHIP('DateTable'[Date], 'Tickets'[Ticket_OpenDate])
)

Closed Tickets = 
CALCULATE(
    COUNTROWS('Tickets'),
    'Tickets'[TicketStatusName] = "Closed",
    USERELATIONSHIP('DateTable'[Date], 'Tickets'[Ticket_CloseDate])
)

Looking forward to your reply.

Best Regards,

Wisdom Wu

[Anonymous · Accepted solution]

Thank you for replying, I realised what was happening was that I had turned off my date filter from interacting with my visual. The measure I wrote worked I just needed the date filter to interact with it. 

[DataInsights]

@Anonymous,

Would you confirm that your model has an inactive relationship using Ticket_CloseDate? This would be in addition to the active relationship using Ticket_OpenDate. You can provide a link to a sample pbix using one of the file services like OneDrive, or provide sample data in table format (not a screenshot). Also provide the expected result.

[Anonymous]

Thank you for replying, I did have the relationship set up my issue is that I had missed that I have visual interaction between my date filter and the visual in question turned off. My measure worked after I turned this back on.