Open Tickets

Super User
2237 Views
Highlighted
Super User
Posts: 9,535
Registered: ‎07-11-2015

Open Tickets

[ Edited ]

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!

 

 

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Attachment
NH Member
Member
Posts: 51
Registered: ‎02-17-2016

Re: Open Tickets

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?

Super User
Posts: 9,535
Registered: ‎07-11-2015

Re: Open Tickets

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)

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


NH Member
Member
Posts: 51
Registered: ‎02-17-2016

Re: Open Tickets

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

Advisor
Posts: 119
Registered: ‎11-18-2015

Re: 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.