Calculate a total count of TicketId within Opening and Close-Date in a selected period

[thomasreick]

Hello there,

I am using one dim_date Table via DAX-CALCULATE to relate to several date columns in my fact_Table to find numbers of opened, solved and closed tickets. The task is to find the number of tickets opened AND closed within a period selected with a slicer.

In the fact_Table are records with an opening-date prior the selected period where only a closing date is available.

Thus made me think of "how to get the count of tickets handled start-to-end from this period"?

Appreciating your input.

thanks in advance

Thomas

[v-kkf-msft · Accepted solution]

Hi @thomasreick ,

I have created the following example data. 

If you want to calculate the ticketid opened and closed during the selected period, please create the following relationships and measure.

Open And Closed = 
CALCULATE (
    DISTINCTCOUNT ( fact_Table[TicketId] ),
    USERELATIONSHIP ( dim_date[Date], fact_Table[DateClosed] )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[thomasreick]

One more thing...., I hope this may help to get my point.

The desired measure should represent an output, which can be achieved using an SQL-Statement like this and shows only the COUNT() aof a subset of all the tickets:

select count(TicketId)
from tblTickets
where (DateOpened between '01.04.2022' and '30.04.2022')
and (DateSolved between '01.04.2022' and '30.04.2022')
and (DateClosed between '01.04.2022' and '30.04.2022')

but..., the time period is taken from a dimDate (Slicer with Year and Month selected).

Can you help?

Thomas

[thomasreick]

Hello ribisht17,

cannot upload data, not allowed to do so. But the task itself should be pretty self explaining.

The runtime of tickes (ideally: opening => solving => close) might overlap into the next month, so the Slicer set to e.g. 2022 May cannot catch the complete "lifetime" of the ticket.

The question is: 

How do I calculate in a DAX Measure how many tickets are [opened (solved) and closed] within May 2022.

I have created 2 measures, each one uses its own relation to dim_Date (my Calendar-Table) whilst the first relation is established 1:n between dim_Date][Datum] and facts.[OpeningDate].

#SolvedTickets=

CALCULATE(

COUNT(facts.[pkFact]),(dim_Date.Datum, facts.[SolvedDate])

)

#ClosedTickets=

CALCULATE(

COUNT(facts.[pkFact]),USERELATIONSHIP (dim_Date.Datum, facts.[ClosedDate])

)

Can you handle this explanation to get the point?

rgds

Thomas

[ribisht17]

@thomasreick 

Please paste some data here

Regards,

Ritesh