cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
speedster
Frequent Visitor

Open Tickets per group by date

Similar to an earlier question (sales by product by date), I am working with some data from ServiceNow and want to display Open Tickets per group per date. Data model is as follows:

 

ServiceNow-datamodel.PNG

 

The measures created on the Incident table are as follows:

 

 

OpenTickets = CALCULATE(COUNTROWS(FILTER(ALL(Incident), Incident[opened_at_date]<=max(DateDimension[Date]) && (Incident[resolved_at_date] > MAX(DateDimension[Date]) ||ISBLANK(Incident[resolved_at_date])))))

OpenTicketsFilter = CALCULATE(COUNTROWS(FILTER(ALLSELECTED(Incident), Incident[opened_at_date]<=max(DateDimension[Date]) && (Incident[resolved_at_date] > MAX(DateDimension[Date]) ||ISBLANK(Incident[resolved_at_date])))))

OpenTicketsPrev = CALCULATE(COUNTROWS(FILTER(ALL(Incident), Incident[opened_at_date]<=(max(DateDimension[Date])-1) && (Incident[resolved_at_date] > (MAX(DateDimension[Date])-1) ||ISBLANK(Incident[resolved_at_date])))))

OpenTicketsFilterPrev = CALCULATE(COUNTROWS(FILTER(ALLSELECTED(Incident), Incident[opened_at_date]<=(max(DateDimension[Date])-1) && (Incident[resolved_at_date] > (MAX(DateDimension[Date])-1) ||ISBLANK(Incident[resolved_at_date])))))

OpenTicketsDelta = [OpenTickets] - [OpenTicketsPrev]

TicketsOpened = CALCULATE(COUNTROWS(FILTER(ALL(Incident), Incident[opened_at_date]=max(DateDimension[Date]))))

TicketsResolved = CALCULATE(COUNTROWS(FILTER(ALL(Incident), Incident[resolved_at_date]=max(DateDimension[Date]))))

 

 

The *filter measures are for use when using a slicer to filter for a specific period. , whereas the other measures are used for when I want to view point-in-time cumulative numbers.

 

What I need to be able to do is report on the count of cumulative opened tickets, tickets opened, tickets resolved per day by things like:

  • The group or agent the ticket is assigned to
  • The agent that resolved the ticket
  • The location of the caller
  • The department of the caller

If I create a matrix with Date, Assignment Group and open tickets this is what I get:

ServiceNow-ticketsbygroup.PNG

 

I can create measures that contain hard-coded filters and use those, but it is not scalable (I don't want to create/remove measures as agents join/leave). How can I report on the data I require? I think I might have to query the data into a new table and use that for the visualisation source?

 

Thanks in advance.

1 ACCEPTED SOLUTION
konstantinos
Memorable Member
Memorable Member

This is called count "events in progress". ( so you can google it )

First you cannot have an active relantionship between the Dates table and the inccident table, so probably you need to create an extra date table or make all realntionships inactive in the one that you use.

 

If you have an active relantionship you will count the tickets open this day and not the actual tickets that are open also from previous days.

 

For solutions you can check these posts from @GBrueckl & @Technitrain

 http://blog.gbrueckl.at/2014/12/events-in-progress-for-time-periods-in-dax/

http://blog.crossjoin.co.uk/2011/11/10/solving-the-events-in-progress-problem-in-dax-v2-0/

 

and check this whiteparer after 16 page http://www.sqlbi.com/wp-content/uploads/DAX-Query-Plans.pdf from Alberto Ferrari

Konstantinos Ioannou

View solution in original post

1 REPLY 1
konstantinos
Memorable Member
Memorable Member

This is called count "events in progress". ( so you can google it )

First you cannot have an active relantionship between the Dates table and the inccident table, so probably you need to create an extra date table or make all realntionships inactive in the one that you use.

 

If you have an active relantionship you will count the tickets open this day and not the actual tickets that are open also from previous days.

 

For solutions you can check these posts from @GBrueckl & @Technitrain

 http://blog.gbrueckl.at/2014/12/events-in-progress-for-time-periods-in-dax/

http://blog.crossjoin.co.uk/2011/11/10/solving-the-events-in-progress-problem-in-dax-v2-0/

 

and check this whiteparer after 16 page http://www.sqlbi.com/wp-content/uploads/DAX-Query-Plans.pdf from Alberto Ferrari

Konstantinos Ioannou

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.