Open Tickets

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.

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

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