Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
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:
If I create a matrix with Date, Assignment Group and open tickets this is what I get:
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.
Solved! Go to Solution.
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
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