Open Tickets 05-15-2020 12:05 PM

[Greg_Deckler]

Thanks to @Phil_Seamark's insightful guidance and examples in his fantastic new book, Beginning DAX with Power BI: The SQL Pro’s Guide to Better Business Intelligence, I finally "get" the GENERATE function and how it can be used to elegantly solve a problem that has vexed me since almost the very first Power BI model that I ever built, dealing with data that contains date ranges.

The following measure assumes a disconnected date table and data that involves "tickets" with open and close dates. The measure computes the number of open tickets at any point in time. This measure can then be used to display how many open tickets there are within a visual based upon the date hierarchy from the date table. Nifty.

Tickets Open = 
VAR tmpTickets = ADDCOLUMNS('Tickets',"Effective Date",IF(ISBLANK([Closed Date]),TODAY(),[Closed Date]))
VAR tmpTable =  
SELECTCOLUMNS(
    FILTER(
        GENERATE(
            tmpTickets,
            'Calendar'
        ),
        [Date] >= [Opened Date] &&
        [Date] <= [Effective Date]
    ),
    "ID",[Ticket Num],
    "Date",[Date]
)
VAR tmpTable1 = GROUPBY(tmpTable,[ID],"Count",COUNTX(CURRENTGROUP(),[Date]))
RETURN COUNTROWS(tmpTable1)

If you are going to own one DAX book, IMHO, Phil's is the book you want!

eyJrIjoiMzYxZjIzZDYtMjA0Yi00ZmNlLWE0YWMtM2Q4NjEwYjE3OGYyIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

[Gaurav_84]

Hi Greg,

Is there any way to bring same data in table. I used your Dex formula to calculate incident count for each month. On bar chart its working fine but can i get same thing is table. for example table have column where i have incident ID, status and number of days incident is open. 

As of now in incident column its shows only 1 as value how ever that incident is open for 6 days.

Regards

Gaurav

[VishalJhaveri]

Refer this video if you want to learn more regarding this topic. Expand the date range in Months i.e., 'Start of month' rather than in days.

Generate Dates between Start and End Date in Power Query - YouTube

[DonPepe]

Hi @Greg_Deckler ,

Thanks for your insight ! it's really sexier than my old solution. 

I managed this situation in Power Query by creating a list of all date between my "open" and "close" date and counting the number of different ticket each day. 
It works but I got a ton load of line. 

Regards,
Don 

[jt1024]

Hello,

Can this work without date heirarchies?  When I tried in my model the measure gave me an error of comparing a date to a true/false?

See attached test model.

Thanks again for any tips you can provide.

Note I'm trying not to use date heirarchies.

- Jim

[chrisx]

I am trying to do this but with 5 stages.  I am trying to figure out the avg time a record spent between each stage, the avg time a record spent from stage 1 to stage 5, and the count of records in each stage. I am having trouble with the count of records in each stage. How do I make it that the record can only exist in one stage at a time?

[NH]

Please help to advise. Based on the ticket table, in Jan 2018, it has 5 ope ticket but one of them (ticket No 5) was closed on the same day. Hence it should has 4 open tickent in Jan 2018? Not sure did I intepret correctly?

[Greg_Deckler]

Good catch, you can fix it via a small tweak:

Tickets Open = 
VAR tmpTickets = ADDCOLUMNS('Tickets',"Effective Date",IF(ISBLANK([Closed Date]),TODAY(),[Closed Date]))
VAR tmpTable =  
SELECTCOLUMNS(
    FILTER(
        GENERATE(
            tmpTickets,
            'Calendar'
        ),
        AND(
            [Date] >= [Opened Date] && [Date] <= [Effective Date],
            NOT([Opened Date]=[Effective Date])
        )
    ),
    "ID",[Ticket Num],
    "Date",[Date]
)
VAR tmpTable1 = GROUPBY(tmpTable,[ID],"Count",COUNTX(CURRENTGROUP(),[Date]))
RETURN COUNTROWS(tmpTable1)

[abehrmann]

How can this be adjusted to show tickets that were open and closed the same day? 

[Brian_M]

Hi Greg, firstly thanks for the great tip on Phil's book - I've just ordered today.  I've never used the GROUBY or CURRENTGROUP so I think that'll be new ammo in the toolbelt (once I understand it!)

A couple of comments, I think perhaps your measure is skewed towards and the thinking required to calculate the number of open days for each ticket, which can be tricky to get to perform well - and your approach is a great one for that. I love and will adopt your use of VAR and temp tables, it really helped me step through and understand the steps.

Below I'll outline my steps through nerdishly trying to optimise the DAX. I'd appreciate any feedback as I'm not always confident that I get the right result.

I think the GROUPBY can be simplified. The version you use is great because it shows the number of days each ticket has been open which is a great way to get that result - I'll use that again for sure.  But, in this instance, I don't think you need the number of open tickets. You just want the number of distinct ID's in tmpTable so I think you could just use:

" VAR tmpTable1 = GROUPBY ( tmpTable,[ID] )"

Stepping it back further, you may not need to SELECTCOLUMNS to get ID and Date, you really just want the distinct list of ID's that survive the FILTER. So this might be a further simplification:

Tickets Open Simpler = 
VAR tmpTickets = ADDCOLUMNS('Tickets',"Effective Date",IF(ISBLANK([Closed Date]),TODAY(),[Closed Date]))

VAR tmpTable =  
SUMMARIZE(
    FILTER(
        GENERATE(
            tmpTickets,
            'Calendar'
        ),
        [Date] >= [Opened Date] &&
        [Date] <= [Effective Date]
    )
    ,Tickets[Ticket Num]
)
RETURN 
COUNTROWS(tmpTable)

My final solution would be even simpler and avoid generating the crossjoin between all the Dates and all the tickets.  We could just find the first (minDate) and last (maxDate) Date in the current filter context and then FILTER the tmpTable to just those tickets opened up to and including the maxDate, and not closed before the firstdate, as follows:

Tickets Open Simpler Again = 
VAR tmpTickets = ADDCOLUMNS('Tickets',"Effective Date",IF(ISBLANK([Closed Date]),TODAY(),[Closed Date]))
VAR minDate = FIRSTDATE('Calendar'[Date])
VAR maxDate = LASTDATE('Calendar'[Date])

VAR tmpTable =  
    FILTER(
        tmpTickets,
        [Opened Date] <= maxDate &&
        [Effective Date] >= minDate
    )
RETURN 
COUNTROWS(tmpTable)

I seem to get the correct results with each of these measure versions, although I find it tricky with DAX to know whether I've just created a version that won't work in certain other contexts.  So many ways to get to the same result is one of the real challenges of DAX, plus the performance can depend on the shape of your data!

I learned a lot from your post and hope that by posting my chain of thought might help others look at how yoiu can step back through such a calculation looking for each different way to optimise.

[IntaBruce]

Hi @Brian_M , I have tried both of your simplifications alongside Greg's original ( @Greg_Deckler ) and I get three different results.  Now I'm really confused 🙃

[Anonymous]

I am curious as to what your method would be if you needed to include time, either as a Datetime or seperate?

[siljewiehauge]

Thanks for the post, it has helped me to understand better, but I'm still not getting the outcome I want. How many open tickets at the end of each month? Now , when I look at it a day basis, it's correct. But I would like to look at it at  month basis. How can I add it to the code?

[NH]

Hi Greg,

Thanks for the tweak. But it seem like you may have to tweak again.

Take for example in month of Feb 2018. 

4 open ticket from Jan 2018  was carry forward to Feb 2018. With ticket ID 3 closed in Feb 2018. So remaining Open ticket count = 3 in Feb 2018.

3 new open ticket in  Feb 2018. But 2 tickets was closed in Feb 2018. So remaining Open ticket count = 1

Total open ticket in Feb 2018 should be 3+1= 4?

Please correct me if my interpreting was wrong.

Best Regard.

NH