I’ve got a report for analysing our support ticket data, a condensed version of which is attached.
We’ve had a request for a drop down to select a project phase, which each have a start/end date, to view the data for tickets created in that time period. Different projects can cross over in dates, so it’s not a case of a single date belonging to a single project phase. I’m really struggling to get my head around what relationships/DAX I need to accomplish this.
The project dates are entered into the pbix below and an extract of the tickets. Any help/advice is appreciated.
Support Ticket Analysis eDNA.pbix (780.4 KB)
Tickets.xlsx (29.9 KB)
I’ve found a way to do this. Instead of having a start & end date on my projects, I create a row for each date between the 2 in power query using the below in a customer column:
{ Number.From([StartDate])… Number.From([EndDate]) }
Expand this list to new rows and change the type to date.
Then a create a relationship between this new date column and my dates table, the slicer then works on the report.
Support Ticket Analysis eDNA.pbix (782.9 KB)
2 Likes
Jamie, this is really cool. You make every IT Manager everywhere proud in how you marched through this effort, self-solved, and shared the results in the way you did. How did this solution scale once the production version was updated?